topic Missing Data in visual Table - Week over Week in DAX Commands and Tips

Missing Data in visual Table - Week over Week

Darrell — Wed, 10 Feb 2021 18:07:15 GMT

The issue is that I am trying to get Week over Week % AND the ability to use Page Filters to affect the totals. First off, just getting the correct sales from last week is giving me problems.

By using these DAX codes, I am missing the "lastWeekRetailSales" for 01/11/2021, which is sales from 01/04/2021 and they do exist.

lastWeekRetailSales = CALCULATE( SUM('Query_Data'[Retail_Sales]), DATEADD('Query_Data'[Start_Date],-7,DAY) )

lastWeekRetailSales = CALCULATE ( SUM ( 'Query_Data'[Retail_Sales] ), FILTER ( ALLSELECTED('Query_Data'), 'Query_Data'[Start_Date] = MAX('Query_Data'[Start_Date]) - 7 ) )

This DAX code provides me with all the Sales from Last Week, but it NOT affected by the Page Filter.

lastWeekRetailSales2 = CALCULATE ( SUM ( 'Query_Data'[Retail_Sales] ), FILTER ( ALL('Query_Data'), 'Query_Data'[Start_Date] = SELECTEDVALUE('Query_Data'[Start_Date]) - 7 ) )

What am I missing? TIA

Re: Missing Data in visual Table - Week over Week

MattAllington — Wed, 10 Feb 2021 19:38:23 GMT

ALLSELECTED by definition will not allow you to access periods that are filtered. You need to use ALL. Also, you don't seem to have a calendar table. You should always use a calendar table for time intelligence https://exceleratorbi.com.au/power-bi-calendar-tables/

when you use ALL(calendar[date]), it should work.

Re: Missing Data in visual Table - Week over Week

Darrell — Thu, 11 Feb 2021 21:22:52 GMT

After building your Calendar, I'm in business. Thanks Matt!