Is there a way to create and display a running total by category in DAX?

Question

I am trying to display a running total by category as a stacked bar chart. I have several pieces which are needed for a certain date. My Y-axis represents the number of pieces needed and the X-axis represents the deadlines. As a legend I would like to add the current state of each reference like this:

Categorized running total by date .

I achieved this by creating a column by status and by filling them with 0 and 1 but I would like to find faster way to do so, which would also work if someone added a category.

My need is to see at one glance at which point of the production process are my pieces and at which date they will be needed.

Using a measure I managed to get the running total, which looks like this :

Running total by date

The current measure is :

CALCULATE(
    COUNTA('Historique LOB'[P/N14.Digits]);
    FILTER(
        ALLSELECTED('Historique LOB'[Besoin concaténé format date]);
        ISONORAFTER('Historique LOB'[Besoin concaténé format date]; MAX('Historique LOB'[Besoin concaténé format date]); DESC)
    )
)

However when I try to add the state as legend, I get this:

Categorized running total

I think that the problem is that Power BI calculates the running total on each category. In a way that if after a certain date there is no piece in a given category the category is just not displayed anymore.

I am surprised I couldn't find any help on this anywhere as this can be very easily done on Excel.

I tried "show all the elements without data" without success. I am now looking for a DAX formula to achieve that visualisation. Any idea on the question ?

My data looks like this :

data

Thank you in advance,

Please read How to Ask, then edit your question to show a sample of your data, the code you've tried so far, and what your required outcome actually looks like. — Olly

Olly Olly · Accepted Answer · 2019-04-24T08:41:38

Create a separate Date table - you could simply use

=CALENDARAUTO()

Create a relationship between 'Date'[Date] and 'Table1'[Besoin Concatenee].

Now create a measure like:

Cumulative Parts = 
VAR DateCurrent = MAX ( 'Date'[Date] )
RETURN
    CALCULATE ( 
        COUNTA ( Table1[Part Number] ),
        FILTER ( 
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= DateCurrent
        )
    )

Now create your Stacked Column chart with

'Date'[Date] in Axis

'Table1'[Status] in Legend

[Cumulative Parts] in Value.

Here's an example PBIX file, with some mocked up data: https://pwrbi.com/so_55811631/

Is there a way to create and display a running total by category in DAX?

1 Answers