Rolling 12m sum with selection

[jrobinson]

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!!

[v-haibl-msft · Accepted solution]

@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]

Thanks for the help! That's worked perfectly!