Forum Discussion
Ho
Hi
I have a table with 2 fields, Day, Sales.
Two questions
1-How can I calculate Sales for different time periods like Yesterday, Last Week, WTD, MTD, and YTD?
2-How can I compare each of those, with its equivalent in the previous year?
Thanks
Hi Anonymous ,
Firstly, suggest you to create a new date table, like below.
Date = CALENDARAUTO()Reference:
Creating Calendar Table in Power BI using DAX Functions
- Yesterday
Yesterday = CALCULATE([Total Sales],PREVIOUSDAY('Date'[Date]))- Last Week
Last Week = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALL ( 'Date' ), WEEKNUM ( 'Date'[Date], 2 ) = WEEKNUM ( TODAY (), 2 ) - 1 ) )These are just samples. We can implement with different ways. You could reference the blogs to have a try.
Creating a dax calculated column for today,yesterday and next working day with variables
Calculate Previous Weeks Sales - Advanced DAX in Power BI
Week to Date Calculation in Power BI with DAX
Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
First of all create a calendar table if you use the same table for calculate time inteligence measures you will get incorrect values due to filtering use.
Regarding the calculations you need you would need to have something similar to the measures below:
YTD = TOTALYTD (SUM(Table[Sales]); Calendar[Date]) MTD = TOTALMTD (SUM(Table[Sales]); Calendar[Date]) YESTERDAY = CALCULATE (SUM(Table[Sales]);FILTER(ALL(Calendar); Calendar[Date] = Today() - 1) Comparision with previous YEAR would be something similar to: YTD PY = CALCULATE([YTD]; DATEADD(Calendar[Date]; -1 ; Year)These are just examples you may need to adjust them to your model or use some other calculations like the DATESBETWEEN or SAMEPERIOSLASTYEAR
Power BI does all of this. You need a Date table (see this for creating one that is dynamic), and a good understanding of Time Intelligence functions. You can start here to see if it enough. But there are some really good books on DAX that have chapters on Time Intelligence.
Hi Anonymous ,
Firstly, suggest you to create a new date table, like below.
Date = CALENDARAUTO()Reference:
Creating Calendar Table in Power BI using DAX Functions
- Yesterday
Yesterday = CALCULATE([Total Sales],PREVIOUSDAY('Date'[Date]))- Last Week
Last Week = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALL ( 'Date' ), WEEKNUM ( 'Date'[Date], 2 ) = WEEKNUM ( TODAY (), 2 ) - 1 ) )These are just samples. We can implement with different ways. You could reference the blogs to have a try.
Creating a dax calculated column for today,yesterday and next working day with variables
Calculate Previous Weeks Sales - Advanced DAX in Power BI
Week to Date Calculation in Power BI with DAX
Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
