Forum Discussion

brekeke's avatar
brekeke
Frequent Visitor
8 years ago
Solved

Moving Average Of Calculated Measure

Hi All,   I would like to calculate the moving average of an already calculated (% of grand total) measure. I've tried a lot of solutions but none of them seem to work correctly.   Table structur...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi brekeke,

     

    Please try this formula.

    Measure 2 =
VAR weekPeriods =
    CALCULATE (
        DISTINCTCOUNT ( tbl_periods_all[Period Column] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( tbl_periods_all[Start Of Period] ),
            -21,
            DAY
        ),
        ALL ( tbl_periods_all[Period Column] )
    )
VAR pPeriods =
    CALCULATE (
        DISTINCTCOUNT ( tbl_periods_all[Period Column] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( 'tbl_periods_all'[Start Of Period] ),
            -7,
            MONTH
        ),
        ALL ( tbl_periods_all[Period Column] )
    )
RETURN
    IF (
        MIN ( 'Period Selector'[period] ) = "Week",
        CALCULATE (
            SUM ( data[fact] ),
            DATESINPERIOD (
                'Calendar'[Date],
                MIN ( 'tbl_periods_all'[Start Of Period] ),
                -21,
                DAY
            ),
            ALL ( tbl_periods_all[Period Column] )
        )
            / weekPeriods,
        CALCULATE (
            SUM ( data[fact] ),
            DATESINPERIOD (
                'Calendar'[Date],
                MIN ( 'tbl_periods_all'[Start Of Period] ),
                -7,
                MONTH
            ),
            ALL ( tbl_periods_all[Period Column] )
        )
            / pPeriods
    )

    Moving_Average_Of_Calculated_Measure2

     

    Best Regards,

    Dale

     

brekeke's avatar
brekeke
Frequent Visitor
8 years ago

Thanks for the advice. It might work but first I need to fully understand your solution. I'm fairly new into power BI and the logic is less than obvious to me sometimes...

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi brekeke,

 

It would be great if you can provide the DUMMY pbix file (Not your real data). I can add the solution to the file and send back to you. Then it could be easier for you to try the solution out.

 

Best Regards,

Dale

  • brekeke's avatar
    brekeke
    Frequent Visitor
    8 years ago

    Thanks v-jiascu-msft,

    I'll try to make a dummy file, but it will take some time I guess.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi brekeke,

         

        I made a few changes in your file. Please check it out here.

        The measure is:

        Measure =
IF (
    MIN ( 'Period Selector'[period] ) = "Week",
    CALCULATE (
        SUM ( data[fact] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( 'tbl_periods_all'[Start Of Period] ),
            -21,
            DAY
        ),
        ALL ( tbl_periods_all[Period Column] )
    ),
    CALCULATE (
        SUM ( data[fact] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( 'tbl_periods_all'[Start Of Period] ),
            -7,
            MONTH
        ),
        ALL ( tbl_periods_all[Period Column] )
    )
)

        Moving_Average_Of_Calculated_Measure

         

        Best Regards,

        Dale