Power BI DAX Running Total with Multiple Filters

Question

I created a monthly rolling amount which sums invoices for the month and displays the summary on the first day of the month. This code works fine, except it displays the running total for all months of the year, even when there is no data (or the month hasn't arrived yet, like Dec 2020).

Running Total InvoiceTotals = 
CALCULATE(
    [InvoiceTotals],
    FILTER(
        CALCULATETABLE(
            SUMMARIZE('Date', 'Date'[YearMonthSort], 'Date'['Date'[FirstDayOfMonth]]),
            ALLSELECTED('Date')
        ),
        ISONORAFTER(
            'Date'[YearMonthSort], MAX('Date'[YearMonthSort]), DESC,
            'Date'['Date'[FirstDayOfMonth]], MAX('Date'['Date'[FirstDayOfMonth]]), DESC
        )
    )
)

--Result
|---------|------------------|---------------|
| Date    | Invoice Total    | Running Total |
|---------|------------------|---------------|
|6/1/2020 |  500             |  500          |
|---------|------------------|---------------|
|7/1/2020 |  700             |  1200         |
|---------|------------------|---------------|
|8/1/2020 |                  |  1200         |
|---------|------------------|---------------|
|9/1/2020 |                  |  1200         |
|---------|------------------|---------------|

I'd like to get rid of the last two rows, which I can with the following code, but I can't combine this filter with the filters above.

Running Total InvoiceTotals = 
CALCULATE(
    [InvoiceTotals],
    FILTER(
        'OrderHeader','OrderHeader'[InvoiceTotals] > 0
    )
)

How can I combine these filters?

EDIT: I replaced older code with my newest set of code.

Maybe you could insert "Invoice Total" column into a Visual Filter (the ones on the right-hand side pane) and filter out blank "Invoice Total" values? — Justyna MK
This is the answer. If you add it as an answer, I'll check it. Thanks. — jabs

Justyna MK Justyna MK · Accepted Answer · 2020-07-28T16:23:44

As discussed in the commentary section, you can try inserting "Invoice Total" column into a Visual Filter (right-hand side pane) and filter out blank "Invoice Total" values. Thanks to this solution you will avoid editing your measure.

Power BI DAX Running Total with Multiple Filters

1 Answers