Including groups with no values in stacked column chart

Question

I'm building a chart that will cumulatively sum Invoice Values for the next month, broken out by category of sale. It looks like this:

My problem is that for particular slicer values, there might not be any invoices for a particular category, and thus the groups just don't show in the graph:

This looks scrappy, so I'm trying to force them to show. Given the rows simply don't exist the way I'm trying to do this is to have a new table which has a row per-date-per-category, and use a measure to cumulatively sum all the data from my source table. So for example given this source table:

I've built the structure of this table, but I need to find a way to add the "Cumulative Value" field that's also shown:

Unfortunately I can't work out how to make that work. The usual cumulative sum syntax would be:

Cumulative Value = (
    CALCULATE(
        SUM('Table 1'[Value]),
        FILTER(ALLSELECTED('Table 1'), ISONORAFTER('Table 1'[Date], MAX('Table 1'[Date]), DESC))
    )
)

And I can't seem to add in another filter expression without either

Breaking it such that it returns different values per category but the same value for every date
Breaking it such that it returns different values per date but the same value for each category

So; what Measure can I build to create that "Cumulative Value" field?

Dan Scally Dan Scally · Accepted Answer · 2019-03-18T11:55:35

Never mind, I got it. Full DAX for the answer was:

CumulativeValue = 

    VAR CurrActionDate = MAX('Table 2'[Date])
    VAR CurrTransType = MAX('Table 2'[Category])

    RETURN (
        CALCULATE(
            SUM('Table 1'[Value],
            FILTER(
                ALLSELECTED('Table 1'),
              'Table 1'[Date] <= CurrActionDate
            ),
            FILTER(
                ALLSELECTED('Table 1'),
                'Table 1'[Category] = CurrTransType
            )
        )
    )

Ta-da! Cumulative sum across different groups with no blank values.

Including groups with no values in stacked column chart

1 Answers