DAX for dynamic date reporting

[Anonymous]

@ links to members, content

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

[Anonymous · Accepted solution]

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.

[Anonymous]

@ links to members, content

Greg I am getting error message on line with my date.  Would you please write out the DAX code.  I dont understand what you mean by filtering.  Do I add the filter in the DAX code or use the filter pane?

[Greg_Deckler]

@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