Running total of a measure which use DATESINPERIOD and months from fact table

Question

I am using a measure below to display the months from fact table as described here:

Billings12Months =
CALCULATE (
    SUM ( 'Datatable'[Allowable] ),
    DATESINPERIOD ( DimDate[Date], MIN ( DimDate[Date] ), +12, MONTH )
)

My attempt to get the running total of above measure is failing:

BillingsRunningTotal =
CALCULATE (
    [Billings12Months],
    FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) )
)

BillingsRunningTotal2 =
SUMX (
    FILTER (
        ALLSELECTED ( DimDate[Date] ),
        DimDate[Date] <= MAX ( ( DimDate[Date] ) )
            && YEAR ( DimDate[Date] ) = YEAR ( MIN ( DimDate[Date] ) )
    ),
    [Billings12Months]
)

[BillingsRunningTotal] return same values as [Billings12Months] (please see screen 1 attached) and [BillingsRunningTotal2] return wrong values and month start from Jan, 17 instead of May, 17 (please see screen-2)

Please help me to calculate the running total. If possible please describe how your solution is working so that I can be better in DAX.

Update:

Please see the screen-3 below for the output when I use the measure suggested by Kosuke:

BillingsRunningTotal =
CALCULATE (
    SUM ( Datatable[Allowable] ),
    FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) )
)

The months are from fact table (not from a Date table) and I think DATESINPERIOD plays a role to calculate and display the months. When we use SUM ( Datatable[Allowable] ), there would be a single month as dictated by the slicer. So we need to use DATESINPERIOD with rolling month calculation logic (DimDate[Date] <= MAX ( DimDate[Date] )) or virtually sum the [Billings12Months], It is where I am failing.

Thanks

If you are calculating rolling total of past 12 months with Billings12Months, then the third argument of DATESINPERIOD should be -12 instead of +12. And I don't understand what you are trying to calculate with BillingsRunningTotal. Can you please update with a description about the desired output? — Kosuke Sakai
+12 is because I want future months of what has been selected in the 'Beginning of Fiscal Year' slicer, not the last 12 months and running total is just, for example, for Jul 17, it is 30,306.95 (May, 17) + 18,566.16 (Jun, 17). — Prakash Gautam
thanks, I understood the Billings12Months. Based on the description, the running total for Jul 17 will be sum of total for May, 17 to Apr, 18 and total for Jun, 17 to May, 18, thus double-counting the value for Jun, 17 to Apr, 18. Is this understanding correct? What will the calculation be for different drill-down levels other than monthly? For example, if drilled down to daily level, should the number for Jul 3, 17 be total for Jul 1, 17 to June 30, 18 + total for Jul 2, 17 to Jul 1, 18? — Kosuke Sakai
No, the running total for July 2017 is simply May 17 + Jun 17 + July 2017, as a normal running total and the total is not required at the day level. — Prakash Gautam

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-12-18T13:27:05

You are almost there with the first attempt, however what to calculate is not [Billings12Months], but SUM( Datatable[Allowable] ).

BillingsRunningTotal =
CALCULATE (
    SUM ( Datatable[Allowable] ),
    FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) )
)

Essentially, [Billings12Months] and [BillingsRunningTotal] are same in calculating the sum of Datatable[Allowable], but the only difference is each measure calculates for different scope of period. Therefore, the right way of thinking is to wrap SUM ( Datatable[Allowable] ) in CALCULATE, with different filter parameters.

Running total of a measure which use DATESINPERIOD and months from fact table

1 Answers