Running total calculation - relative filtering

Question

i have created a measure to calculate a running invoice total (see below) and it all works fine. When i add it to a visual i want the graph to reset to zero when i use a relative time filter.

For example, the invoice spend for the year might £1000 but i want the spend to reset to zero if i were to apply a filter for say the last 3 months, i don't want it show the total spend but starting from 3 months ago.

Here is my measure:

Running Total COLUMN = CALCULATE (SUM ( 'spend report'[sum(Invoice Spend)]),FILTER(ALLEXCEPT

('spend report','spend report'[Supplier - Supplier Global Ultimate Parent (enr)]),'spend repor'[Accounting Date]<= MAX('spend repor'[Accounting Date])))

jihwan_kim · Accepted Answer

Hi,
I am not sure how your datamodel looks like, but please try the below and check whether it suits your requirement.
&nbsp;
Running Total COLUMN =
CALCULATE (
    SUM ( 'spend report'[sum(Invoice Spend)] ),
    FILTER (
        ALLSELECTED ( 'spend report' ),
        'spend repor'[Accounting Date] &lt;= MAX ( 'spend repor'[Accounting Date] )
    ),
    VALUES ( 'spend report'[Supplier - Supplier Global Ultimate Parent (enr)] )
)

Forum Discussion

Running total calculation - relative filtering

2 Replies

Related content

Relative % calculation

Calculating with Filters

Time Relative filter

Calculate Filters

DAX Calculation - Find value based on day (relative)