cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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!:
Mastering Power BI 2nd Edition

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors