Refresh every Monday - report needs to show last 4 weeks starting that past Friday. How?

[whatisdata96]

Hi all,

Title sounds a bit odd, but here's what I'm trying to do.

Our reporting 'work week' start on Saturday and end on a Friday. In my date table, I created a column that groups by Fridays which counts as a Week 'end' date. I refresh the report on Monday but have to modify what dates are shown.

Today's date is Sunday, October 9th. Tomorrow, on Monday, when I refresh my report, I want my pivot table to show 10/7/22, 9/30/22, 9/23/22, and 9/16/22. To clarify, for 10/7/22 (for example), the data would capture whatever happened 10/1-10/7. For 9/30, it'd be 9/24-9/30. 

Now I have already created a column that groups it correctly like that. I just need to add some sort of time-intelligence function to understand that we only want 4 full weeks showing starting from the last Friday that we just passed.

It would be so much easier if all we had to do was show the last x amount of days/weeks/months from time of refresh, but that's not the case here. I'm thinking maybe we'd have to rank the week column i made? and leverage that?

I'm so stumped. Any help would be appreciated.

[whatisdata96]

Is it because my end of week calculated column was done inccorectly?

This is how I made that column if it makes any difference: =dimCalendar[Date] - MOD(dimCalendar[Date]-0, 7) + 6

[whatisdata96]

Thank you so much for replying. I have done exactly as you did and something odd came up..

As you can see, I created 2 pivot tables. On the left, I used the normal date column (in dimCalendar) and it looks like it has all of the correct numbers.

On the right side, I'm using the calculated column I made to group all aggregation by End of Week (Friday) Why am I getting repeat values like this?

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution for your datamodel.

Thanks.

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Recent 4 weeks qty: =
VAR _today =
    TODAY ()
VAR _newtable =
    FILTER ( ALL ( 'Calendar'[End of Week] ), 'Calendar'[End of Week] < _today )
VAR _top4table =
    TOPN ( 4, _newtable, 'Calendar'[End of Week], DESC )
RETURN
    CALCULATE ( [QTY total:], KEEPFILTERS ( _top4table ) )