Cumulative total of a daily measure

[Anonymous]

Hi All,

I have the following table:

I'm looking for the required DAX such that column 'test 3' is the cumulative total of the prior daily targets. The daily target is a measure. I can't use a calculated column because the daily target is made of of various product lines and my users want to be able to slice performance against daily target by product line.

So how does one perform a cumulative measure of a daily target?

I've already read this: https://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/ and it didn't help me.

[Zubair_Muhammad]

Hi @Anonymous

Give this a shot please

=
SUMX (
    FILTER (
        ALL ( TableName[Dates] ),
        TableName[Dates] < SELECTEDVALUE ( TableName[Dates] )
    ),
    [Daily Target (measure)]
)

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Please Try this one

=
SUMX (
    FILTER (
        ALLSELECTED ( TableName[Dates] ),
        TableName[Dates]
            < SELECTEDVALUE (
                TableName[Dates]
                    && WEEKDAY ( TableName[Dates], 2 ) IN { 1, 2, 3, 4, 5 }
            )
    ),
    [Daily Target (measure)]
)

[Anonymous]

This DAX throws an error for me. Column " cannot be found or may not be used in this expression.

I think what you meant is:

= SUMX ( FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= SELECTEDVALUE(Dates[Date]) && WEEKDAY ( Dates[Date], 2 ) IN { 1, 2, 3, 4, 5 } ), [Daily Target (meas)] )

I changed the '<' to '<=' on the dates comparator to get the cumulative sum to start on the first row.

This is an AWESOME solution, thank you ever so much!!

[Anonymous]

Hi @Zubair_Muhammad,

Thats >95% there! My Dates table has a concept of WorkDay... I need the cumulative for Workdays only, also the ALL filter on dates means the cumulative sum starts from several years earlier. My report has dates filtered, i'm really looking for the cumulative that meets the currently selected date range (i.e. starting from 1 Jan 2018 should ignore prior years targets).