Rolling Sum Calculation

[KG1]

Hi I need to replicate the WIP weeks formula in Powerbi

Can anyone please advise how I would go about this?

Thank you in advance

[Anonymous · Accepted solution]

Hi @KG1 ,

Please check this measure

Measure =
VAR _3months =
    DATESINPERIOD ( 'Table'[Year Month], MAX ( 'Table'[Year Month] ), -3, MONTH )
VAR _sum1 =
    CALCULATE ( SUM ( 'Table'[Value] ), _3months )
VAR _sum2 =
    CALCULATE ( SUM ( 'Table'[Turnover] ), _3months )
RETURN
    _sum1 * SUM ( 'Table'[WIP] ) / _sum2

Best Regards,

Stephen Tao

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

[KG1]

@Anonymous 

Sorry to bother you again but I cant get the measure to calculate correctly.

I now have the data used in the measure split into 3 tables

Here is the measure I have used 

Calc =

VAR _3months =

DATESINPERIOD ( 'Date Table'[Month / Year], MAX ( 'Date Table'[Month / Year] ), -3, MONTH )

VAR _sum1 =

CALCULATE ( SUM ( 'WIP Weeks'[WIP Weeks] ), _3months )

VAR _sum2 =

CALCULATE ( SUM ( 'WIP & T/O'[Value]), FILTER('WIP & T/O', 'WIP & T/O'[Type] = "Contract Turnover" ), _3months )

RETURN

_sum1 * (calculate([Sum WIP] ) / _sum2)

Here is the result

This is what the result Should be

I can't work out what I am doing wrong

Thank you in advance 

NB: Please only use from July onwards as the measure calculates the last 3 months and I have added in any data pre April 21

[KG1]

Brilliant thanks so much Stephen

[Greg_Deckler]

@KG1 Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128