Forum Discussion

jrobinson's avatar
jrobinson
Advocate IV
9 years ago
Solved

Rolling 12m sum with selection

Hi,   I'm fairly new to Power BI and I am having difficulty creating a rolling 12m sum measure to be used in tables and charts. I have created a mock up of the table, I am aiming for in excel for i...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    jrobinson

     

    I created a test Table1 like below. You can use this measure to get what you want.

     

     

    Measure =
IF (
    FIRSTDATE ( Table1[PeriodDate] )
        >= DATEADD ( FIRSTDATE ( ALL ( Table1[PeriodDate] ) ), 11, MONTH ),
    CALCULATE (
        SUM ( Table1[Value] ),
        DATESINPERIOD (
            Table1[PeriodDate],
            FIRSTDATE ( Table1[PeriodDate] ),
            -12,
            MONTH
        ),
        ALLEXCEPT ( Table1, Table1[PeriodDate], Table1[CompanyName] ),
        Table1[IncludeFlag] = 1
    ),
    "N/A"
)

    If you want a filter to select the companies, you can add the CompanyName in a slicer.

    “N/A” or other word is recommended. Because blank is ambiguous.

     

     

    Best Regards,
    Herbert

     

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

jrobinson

 

I created a test Table1 like below. You can use this measure to get what you want.

 

 

Measure =
IF (
    FIRSTDATE ( Table1[PeriodDate] )
        >= DATEADD ( FIRSTDATE ( ALL ( Table1[PeriodDate] ) ), 11, MONTH ),
    CALCULATE (
        SUM ( Table1[Value] ),
        DATESINPERIOD (
            Table1[PeriodDate],
            FIRSTDATE ( Table1[PeriodDate] ),
            -12,
            MONTH
        ),
        ALLEXCEPT ( Table1, Table1[PeriodDate], Table1[CompanyName] ),
        Table1[IncludeFlag] = 1
    ),
    "N/A"
)

If you want a filter to select the companies, you can add the CompanyName in a slicer.

“N/A” or other word is recommended. Because blank is ambiguous.

 

 

Best Regards,
Herbert

 

  • jrobinson's avatar
    jrobinson
    Advocate IV
    9 years ago

    Thanks for the help! That's worked perfectly!