cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculating the cumulative sum of the previous 2 wks

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

Thanks

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

@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 -

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors