Calculating the cumulative sum of the previous 2 wks

[alvin_alpha]

hi all,,

Has anyone went thru on calculating the "CurrentWeek = Cummulative sum of the Past 2Wks".

For the table below: Prev2Wks_Cummulative_SUM = "W01 QTY + W02 QTY".

Can please help to guide me how to calculate such scenarios?

Thanks

[v-lili6-msft · Accepted solution]

hi  @alvin_alpha 

First, Week column in your table is a text column, so you need a rank/weekno column for this text week column.

Second, you could use this simple formula to get your requirment:

Result = 
CALCULATE(SUM('Table'[QTY]),FILTER('Table','Table'[WeekNum]=EARLIER('Table'[WeekNum])-1||'Table'[WeekNum]=EARLIER('Table'[WeekNum])-2))

or use a conditional to exclude first two rows.

Result = IF(COUNTROWS(FILTER('Table','Table'[WeekNum]=EARLIER('Table'[WeekNum])-1||'Table'[WeekNum]=EARLIER('Table'[WeekNum])-2))=2,
CALCULATE(SUM('Table'[QTY]),FILTER('Table','Table'[WeekNum]=EARLIER('Table'[WeekNum])-1||'Table'[WeekNum]=EARLIER('Table'[WeekNum])-2)))

 Result:

here is sample pbix file, please try it.

Regards,

Lin

[Greg_Deckler]

@alvin_alpha Best if you have an Index column or actual date column to use. Then one pattern is MTBF to refer to earlier rows. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

That's if you want DAX. If you want Power Query let me know, just @ me in reply.

[amitchandak]

@alvin_alpha , for that you need create a week rank on week year column on week column, prefer to have separate week/date table.

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)  ///You use Week , any correct sortable column

last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1

&& 'Date'[Week Rank]>=max('Date'[Week Rank])-3))
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Week -

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-La...