Recursive DAX Cumulative Total

0
votes

I have a measure which multiplies a percentage reduction in debt values by the $ value of sales in the previous month to give a $ benefit. This is:

Gross Benefit:= [Change in 0-30 %] * [Ent Prior Month Credit Sales]

For each Fiscal Month (eg. "01 - July"), the Gross benefit can be calculated. The net benefit will be the gross benefit for that month minus the sum of the net benefits for all the previous months (so I'm not double-counting a benefit). This is a confusing concept and aspect of our system, where we need a snapshot view of the YTD. It is intended to output a table like this:

       | Jun 15 | Jul 15 | Aug 15 | Sep 15 |
Gross  |   0.7  |   0.7  |  0.4   |  0.2   |
Net    |   0.7  |   0.0  | (0.3)  | (0.1)  |

My code to attempt creating this is:

Net Benefit :=[Gross Benefit]
- CALCULATE (
    SUMX ( VALUES ( Calendar[FiscalMonth] ), [Gross Benefit] ),
    FILTER (
        ALL ( Calendar ),
        Calendar[FiscalMonth] < MAX ( Calendar[FiscalMonth] )
    )
)

This just returns blanks. What can I do?

2
powerpivotdaxcumulative-sum
What's the structure of the FiscalMonth column? It is 201605 or just 05? – Orlando Mezquita
How did you get the first table ? using what formula ? and what is [Ent Prior Month Credit Sales] and [Change in 0-30 %] ? measures as well ? – Ravi Sankar Raju
The first table is achieved in standard excel with fixed references to the cells to subtract. Both fields you mention are measures. – JustNuts

2 Answers

0
votes

i think that's because of this line

SUMX ( VALUES ( Calendar[FiscalMonth] ), [Gross Benefit] )

SUMX tries to aggregate [Gross Benefit] from the table VALUES(Calendar[FiscalMonth]) which has only one column and that is not Gross Benefit and so it sums blank.

This might work instead

Net Benefit := [Gross Benefit] - CALCULATE (
                  [Gross Benefit],
                  FILTER (
                         ALL ( Calendar ),
                         Calendar[FiscalMonth] < MAX (Calendar[FiscalMonth])
                  )
               )

Summarize can be used to project necessary columns whereas VALUES project only one column

The solution assumes FiscalMonth and Gross Benefit to be in the same table. Summarize will not work otherwise. Let me know if this works.

0
votes

I have got closer to a solution by breaking the formula down. The [Net Benefit] is:

The [Gross Benefit] less the sum of all [Net Benefit]s for each month previously.

So I created three measures. 1 - [Gross Benefit]

Gross Wave Benefit 0-30 YTD:=( [Change in 0-30 %] * [Ent Prior Month Credit Sales] )

Both of these are measures calculated in a given context. This measure produces correct results. 2. - [Sum of Previous Benefits]

[Sum of Previous Benefits]:=CALCULATE (
SUMX (
    FILTER (
        ALL ( Calendar ),
        Calendar[FM] < MAX ( Calendar[FM] )
            && Calendar[FY] = MAX ( Calendar[FY] )
    ),
    [Gross Wave Benefit 0-30 YTD]
))

The Summary measure is [Net Benefit]

Net Wave Benefit 0-30 YTD:=[Gross Wave Benefit 0-30 YTD]-[Sum of previous benefit]

This is a purer description of the problem but still encounters the recursive/circular reference error.