Forum Discussion
DAX for dynamic date reporting
I have tried to write a dax formula to have my report open dynamically with prior weeks data with no success. The week starts on Monday and ends on Sunday . If I run the report on 12/1/2020 the data for 11/30/2020 should show and If I run the report on 12/8/2020 the data for 12/7/2020.
Any help would be greatly appreciated.
Here is an example:
| Region | Date | Sales |
| Central | 11/30/2020 | 2984029.0 |
| South | 12/7/2020 | 8290404.0 |
Hi Anonymous ,
According to your description, I created the data:
Here are the steps you can follow：
1. Create measure.
Measure 2 = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]=TODAY()-1))2. Result.
Today is December 17, 2020, so it shows the data on December 16, 2020
You can downloaded PBIX file from here.
If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous You will need to use WEEKNUM with a second parameter of 2. You would use this in a calculated column or measure to do something like WEEKNUM(TODAY(),2), - WEEKNUM([Date],2) and you will then need to filter for current year and then this column/measure = 1
Hi Anonymous ,
According to your description, I created the data:
Here are the steps you can follow：
1. Create measure.
Measure 2 = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]=TODAY()-1))2. Result.
Today is December 17, 2020, so it shows the data on December 16, 2020
You can downloaded PBIX file from here.
If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
