Use Cases of DAX in Date Columns in Power BI
PowerBI Course.
DAX provides robust functionalities for working with date columns in Power BI, enabling users to perform a wide range of time intelligence and date-based calculations. By leveraging these DAX functions, you can create comprehensive and insightful reports that effectively analyze temporal data.
1. Year-to-Date (YTD) Calculations
Calculate the cumulative total from the beginning of the year to the current date.
DAX
YTD_Sales = CALCULATE(
SUM(Sales[TotalSales]),
DATESYTD(Date[Date])
)
2. Month-to-Date (MTD) Calculations
Compute the cumulative total from the start of the month to the current date.
DAX
MTD_Sales = CALCULATE(
SUM(Sales[TotalSales]),
DATESMTD(Date[Date])
)
3. Quarter-to-Date (QTD) Calculations
Calculate the cumulative total from the beginning of the quarter to the current date.
DAX
QTD_Sales = CALCULATE(
SUM(Sales[TotalSales]),
DATESQTD(Date[Date])
)
4. Same Period Last Year (SPLY)
Compare the current period's data with the same period in the previous year.
DAX
SPLY_Sales = CALCULATE(
SUM(Sales[TotalSales]),
SAMEPERIODLASTYEAR(Date[Date])
)
5. Rolling 12 Months Calculation
Calculate the total sales over the last 12 months dynamically.
DAX
Rolling_12Months_Sales = CALCULATE(
SUM(Sales[TotalSales]),
DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH)
)
6. Previous Month Calculation
Calculate the total sales for the previous month.
DAX
PreviousMonth_Sales = CALCULATE(
SUM(Sales[TotalSales]),
PREVIOUSMONTH(Date[Date])
)
7. Year-Over-Year (YoY) Growth
Calculate the year-over-year growth percentage.
DAX
YoY_Growth =
DIVIDE(
(SUM(Sales[TotalSales]) - CALCULATE(SUM(Sales[TotalSales]), SAMEPERIODLASTYEAR(Date[Date]))),
CALCULATE(SUM(Sales[TotalSales]), SAMEPERIODLASTYEAR(Date[Date]))
)
8. Custom Date Ranges
Create measures for custom date ranges, such as the last 30 days.
DAX
Last_30Days_Sales = CALCULATE(
SUM(Sales[TotalSales]),
DATESINPERIOD(Date[Date], TODAY(), -30, DAY)
)
9. Week-to-Date (WTD) Calculations
Compute the cumulative total from the start of the week to the current date.
DAX
WTD_Sales = CALCULATE(
SUM(Sales[TotalSales]),
DATESINPERIOD(Date[Date], MAX(Date[Date]), -7, DAY)
)
10. Fiscal Year Calculations
Perform calculations based on a fiscal year that does not align with the calendar year.
DAX
Fiscal_YTD_Sales = CALCULATE(
SUM(Sales[TotalSales]),
DATESYTD(Date[Date], "6/30")
)
Comments
Post a Comment