Power BI Tips & Tricks

Time Intelligence Dax Functions - Power BI TOTALYTD: Calculates the year-to-date total for a given expression. Example:  Total Sales YTD = TOTALYTD(SUM('Sales'[Amount]), 'Date'[Date]) TOTALQTD: Calculates the quarter-to-date total for a given expression. Example:  Total Sales QTD = TOTALQTD(SUM('Sales'[Amount]), 'Date'[Date]) TOTALMTD: Calculates the month-to-date total for a given expression. Example:  Total Sales MTD = TOTALMTD(SUM('Sales'[Amount]), 'Date'[Date]) DATESYTD: Returns a table that contains all dates from the beginning of the year up to the specified date. Example: Sales YTD = CALCULATE(SUM('Sales'[Amount]), DATESYTD('Date'[Date])) DATESQTD: Returns a table that contains all dates from the beginning of the quarter up to the specified date. Example: Sales QTD = CALCULATE(SUM('Sales'[Amount]), DATESQTD('Date'[Date])) DATESMTD: Returns a table that contains all dates from the beginning of the mon...

Sameperiodlastyear till to date based on selection in year and month selection Problem:  If the current year and month are selected, sales data from the same period of the previous year up to the current date is needed. For instance, if it's March 2024, you'd require sales data from March 2023 up to today's date. If the selection is for a different year and month, such as January 2023, you need the entire sales data for January 2022. Solution:  For this scenario you need to create Three Dax Measures Measure 1 = (This measure is to calculate current month sales till the date) CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) ) Explanation: CALCULATE : This is a DAX function used to modify or filter the context for a calculation. It can apply additional filters to a ...