Forum Discussion

jrobinson's avatar
jrobinson
Icon for Advocate IV rankAdvocate 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 illustration:

 

 

The SQL data table I am using has the structure PeriodName | CompanyName | AccountDescription | Value. I have added columns so that the resultant table has the structure PeriodName | PeriodID | PeriodDate| CompanyName | AccountDescription | Value | IncludeFlag

 

For further information,

  • PeriodName is the name of financial periods, not calendar months. I have added a date column with a one-to-one relationship with 'PeriodName' (which is the first date of the month i.e. 201601 = 01/07/2015, ... , 2016012 = 01/06/2016) as well as an integer PeriodID column with a one-to-one relationship (i.e. 201601 = 1, 201602 = 2, ..., 201712 = 24) which have been taken from my master date table.
  • The include flag is a text field with value 1 or 0 based on the account description. I only want to sum up the value field for those accounts with IncludeFlag = 1.
  • I have a filter pane which allows companies to be (un)selected by the user.

Any help would be greatly appreciated, since I can't seem to generate a calculated measure which gives me exactly what I'm looking for!!

 

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

     

2 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Advocate IV rankAdvocate IV
      9 years ago

      Thanks for the help! That's worked perfectly!