Running Total with funky measure

[KamilSzpyrka]

Hi guys,

I have a case, in which I need to sum up all the Plan for given year and distribute it between quarters depending on how many months in a quarter had this Plan in first place. To do so I need to find first and last month with Plan and distribute it between quarters. Screenshot below presents my goal. Source data on the left, result on the right.

To do so I have created measure as below.

Plan Per Quarter = 
VAR MinYear = 
    CALCULATE (
        MIN ( dimCzas[Rok] ),
        FILTER ( ALL ( dimCzas ), [Plan Total] <> 0 ))
VAR MaxYear = 
    CALCULATE (
        MAX ( dimCzas[Rok] ),
        FILTER ( ALL ( dimCzas ), [Plan Total] <> 0 ))
VAR MinQ =
    CALCULATE (
        MIN ( dimCzas[Rok - Kwartał] ),
        FILTER ( ALL ( dimCzas ), [Plan Total] <> 0 )
    )
VAR MaxQ =
    CALCULATE (
        MAX ( dimCzas[Rok - Kwartał] ),
        FILTER ( ALL ( dimCzas ), [Plan Total] <> 0 )
    )
VAR MinMonth =
    CALCULATE (
        MIN ( dimCzas[Miesiąc] ),
        FILTER (
            ALL ( dimCzas ),
            AND ( dimCzas[Rok - Kwartał] = MinQ, [Plan Total] <> 0 )
        )
    )
VAR MaxMonth =
    CALCULATE (
        MAX ( dimCzas[Miesiąc] ),
        FILTER (
            ALL ( dimCzas ),
            AND ( dimCzas[Rok - Kwartał] = MaxQ, [Plan Total] <> 0 )
        )
    )
VAR MonthsWithPlanInYear =
    IF (
        SELECTEDVALUE ( dimCzas[Rok] ) = MinYear,
        13 - MinMonth,
        IF ( SELECTEDVALUE ( dimCzas[Rok] ) = MaxYear, MaxMonth, 12 )
    )
VAR MonthsWithPlanInQ =
    IF (
        AND (
            MIN(dimCzas[Miesiąc]) < MinMonth,
            SELECTEDVALUE ( dimCzas[Rok - Kwartał] ) = MinQ
        ),
        MinMonth - MAX(dimCzas[Miesiąc]) + 1,
        IF (
            AND (
                MAX(dimCzas[Miesiąc]) > MaxMonth,
                SELECTEDVALUE ( dimCzas[Rok - Kwartał] ) = MaxQ
            ),
           MaxMonth -  MIN(dimCzas[Miesiąc]) - 1,
            3
        )
    )
VAR PlanPerMonth =
    CALCULATE ( [Plan Total], ALLEXCEPT ( dimCzas, dimCzas[Rok] ) ) / MonthsWithPlanInYear
VAR Result = PlanPerMonth * MonthsWithPlanInQ
RETURN
    Result

 I had hard time with this, but it seems to be calcularing correctly (screenshot below).

The problem is when I create Running Total measure, it's not working properly (it should sum everything from the start until given point). Definition of the measure is below.

Running Total Plan Per Q = 
VAR MaxQ =
    CALCULATE (
        MAX ( dimCzas[Rok - Kwartał] ),
        FILTER ( ALL ( dimCzas[Rok - Kwartał] ), [Plan Per Quarter] <> 0 )
    )
VAR Result =
    IF (
        SELECTEDVALUE ( dimCzas[Rok - Kwartał] ) <= MaxQ,
        CALCULATE (
            [Plan Per Quarter],
            FILTER ( ALL ( dimCzas[Rok - Kwartał] ), dimCzas[Rok - Kwartał] <= MAX ( dimCzas[Rok - Kwartał] ) )
        ),
        BLANK ()
    )
RETURN
    Result

Is there any way to obtain Running Total for formula like this?

Many Thanks in advance! Kamil

[AlexisOlson]

I think you're making things more complicated than necessary.

Here's how I'd do it:

Plan Total =
VAR AvgMonthlyPlan =
    AVERAGEX ( FILTER ( ALL ( dimCzas ), dimCzas[Plan] <> 0 ), dimCzas[Plan] )
RETURN
    SUMX ( FILTER ( dimCzas, dimCzas[Plan] <> 0 ), AvgMonthlyPlan )

Cumulative Plan Total =
CALCULATE (
    [Plan Total],
    FILTER ( ALL ( dimCzas ), dimCzas[Month] <= MAX ( dimCzas[Month] ) )
)

[KamilSzpyrka]

Hey Alexis, thanks for fast response in first place! I thought AVERAGEX could be an answer.

Unfortunetly your solution does not resolve some cases (I might have missed them points in my first post).

1) I do not have Plan in this table. It is a measure calculated based on couple of other tables.

2) We can have "empty" months, which also should be considered for calculation. I hope screenshot from excel reflects how should it work.

3) More then one year can be included.

[AlexisOlson · Accepted solution]

It's definitely more difficult to answer when columns in your example table aren't actually columns in your table...

Having gaps and multiple years does make things more complicated but I think you can still simplify. Something like this perhaps:

Plan Total =
SUMX (
    VALUES ( dimCzas[Year] ),
    VAR CurrYear = CALCULATE ( MAX ( dimCzas[Year] ) )
    VAR FilterTable = FILTER ( ALL ( dimCzas ), dimCzas[Year] = CurrYear && dimCzas[Plan] <> 0 )
    VAR YearTotal = CALCULATE ( SUM ( dimCzas[Plan] ), FilterTable )
    VAR LastMonth = CALCULATE ( MAX ( dimCzas[Month] ), FilterTable )
    VAR FirstMonth = CALCULATE ( MIN ( dimCzas[Month] ), FilterTable )
    VAR MonthsTable =
        FILTER (
            dimCzas,
            dimCzas[Year] = CurrYear
                && dimCzas[Month] <= LastMonth
                && dimCzas[Month] >= FirstMonth
        )
    VAR AvgMonthlyPlan = YearTotal / ( LastMonth - FirstMonth + 1 )
    RETURN
        SUMX ( MonthsTable, AvgMonthlyPlan )
)

Cumulative Plan Total =
    CALCULATE ( [Plan Total], ALL ( dimCzas ), dimCzas[YearMonth] <= MAX ( dimCzas[YearMonth] ) )

 This cumulative measure requires creating a calculated column [YearMonth] to be written this simply.

YearMonth = DATE ( dimCzas[Year], dimCzas[Month], 1 )

[KamilSzpyrka]

Alex! Many, many thanks! You helped me with this case, but to understand wider picture of DAX as well.

I've slightly modified your measure, so middle years are always taken from 1 to 12 months and finished with formula as below. Many thanks again!

Plan Total Rozłożony = 
SUMX (
    VALUES ( dimCzas[Rok] ),
    VAR MinYear = CALCULATE (MIN ( dimCzas[Rok] ), FILTER ( ALL ( dimCzas ), [Plan Total] <> 0 ))
    VAR MaxYear = CALCULATE (MAX ( dimCzas[Rok] ), FILTER ( ALL ( dimCzas ), [Plan Total] <> 0 ))
    VAR CurrYear = CALCULATE ( MAX ( dimCzas[Rok] ) )
    VAR FilterTable = FILTER ( ALL ( dimCzas ), dimCzas[Rok] = CurrYear && [Plan Total] <> 0 )
    VAR YearTotal = CALCULATE ( [Plan Total], FilterTable )
    VAR LastMonth = IF(CurrYear = MaxYear, CALCULATE ( MAX ( dimCzas[Miesiąc] ), FilterTable ), 12)
    VAR FirstMonth = IF(CurrYear = MinYear, CALCULATE ( MIN ( dimCzas[Miesiąc] ), FilterTable ), 1)
    VAR MonthsTable =
        FILTER (
            dimCzas,
            dimCzas[Rok] = CurrYear
                && dimCzas[Miesiąc] <= LastMonth
                && dimCzas[Miesiąc] >= FirstMonth
        )
    VAR AvgMonthlyPlan =  YearTotal / ( LastMonth - FirstMonth + 1 )
    RETURN 
        SUMX ( MonthsTable, AvgMonthlyPlan )
)