Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
Solved! Go to 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
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
@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.
@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...
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
103 | |
68 | |
47 | |
39 | |
37 |