Forum Discussion

KG1's avatar
KG1
Icon for Resolver I rankResolver I
4 years ago
Solved

3 Month Rolling Sum Measure

Hi I need to replicate the WIP weeks formula in Powerbi

 

 

 

I have the data 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

Need Help
  • KG1's avatar
    KG1
    4 years ago

    Hi - I worked out it was something to do with the date relationship. I split out the measure as you said to make sure the values were correct (which they were). I then changed the relationship filter direction to both and this has fixed it. Thank you for your help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, try to debug the single variable in the line context.
    What var _sum1 and var _sum2 show in the line context?

    • KG1's avatar
      KG1
      Icon for Resolver I rankResolver I
      4 years ago

      This is out of my comfort zone  - I have used DAX Studio - is this what you mean?

      // DAX Query
      DEFINE
        VAR __DS0Core = 
          SELECTCOLUMNS(
            KEEPFILTERS(
              FILTER(
                KEEPFILTERS(
                  SUMMARIZECOLUMNS(
                    'Date Table'[Date],
                    'WIP & T/O'[Contract],
                    "Calc", 'WIP & T/O'[Calc],
                    "Sum_Turnover", 'WIP & T/O'[Sum Turnover],
                    "Sum_WIP", 'WIP & T/O'[Sum WIP],
                    "SumWIP_Weeks2", CALCULATE(SUM('WIP Weeks'[WIP Weeks])),
                    "HasDataWIP___T_O", IGNORE(
                      CALCULATE(NOT(ISEMPTY('WIP & T/O')))
                    )
                  )
                ),
                OR(
                  OR(
                    OR(
                      NOT(ISBLANK([Calc])),
                      NOT(ISBLANK([Sum_Turnover]))
                    ),
                    NOT(ISBLANK([Sum_WIP]))
                  ),
                  [HasDataWIP___T_O]
                )
              )
            ),
            "'Date Table'[Date]", 'Date Table'[Date],
            "'WIP & T/O'[Contract]", 'WIP & T/O'[Contract],
            "Calc", [Calc],
            "Sum_Turnover", [Sum_Turnover],
            "Sum_WIP", [Sum_WIP],
            "SumWIP_Weeks2", [SumWIP_Weeks2]
          )

        VAR __DS0PrimaryWindowed = 
          TOPN(501, __DS0Core, 'WIP & T/O'[Contract], 1, 'Date Table'[Date], 1)

      EVALUATE
        __DS0PrimaryWindowed

      ORDER BY
        'WIP & T/O'[Contract], 'Date Table'[Date]

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        What i mean is to create a measure that return the single variable and put it in the table. So you can see what returns.

        A measure that return _sum1 and another measure that return _sum2

        Another question why you have a separate table for the week?

        You can upload the pbix?