Forum Discussion

KG1's avatar
KG1
Resolver I
4 years ago
Solved

Rolling Sum Calculation

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      KG1
      Resolver I
      4 years ago

      Brilliant thanks so much Stephen

    • KG1's avatar
      KG1
      Resolver I
      4 years ago

      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