This week vs. last week cumulative

[Anonymous]

Hi All,

I'm a bit stuck with something. I need to deliver a daily report of support requests. As part of this, I need to show (dynamically) a weekly status.

For example, I need to show a cumulative total of all requests received from Jan 1 2021 (min date in my column) till this time last week (5th March 2021 - my 'previous' week) vs. cumulative total of all requests received from Jan 1 2021 to this week (12th March 2021 - my 'current' week).

Essentially it's a view that shows this week vs. same day last week but the calculations should be cumulative (so it shouldn’t look at just the data for the week but rather all data leading and including that week).

I feel like I need to create a dynamic tag in my date column that marks the dates as either this week or previous week dynamically but I'm not sure how to go about it. The reason i'm after a custom column is because i want to use the tag as a filter on a visual. 

Is anyone able to shed some light?
 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Please try this measure:

Cumulative to this week = 
SUMX(FILTER(ALL(Sheet57),Sheet57[Date]<=TODAY()&&Sheet57[Date]>=DATE(2021,01,01)),Sheet57[Value])

Cumulative to last week same day = 
SUMX(FILTER(ALL(Sheet57),Sheet57[Date]>=DATE(YEAR(TODAY()),01,01)&&Sheet57[Date]<=TODAY()-7),Sheet57[Value])

My Sample is from 2021/01/01 to current day 2021/03/16.

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@Anonymous , Try this with separate Date or Year/week table

example

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
Last week YTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week])-1))

or

YTD=
var _max = MaxX(allselected('Date'),'Date'[Week])
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= _max ))

Last week YTD =
var _max = MaxX(allselected('Date'),'Date'[Week])
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= _max-1))

Also, check these

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8