PowerBI DAX - Running total using ISONORAFTER includes previous dates

0
votes

Situation:

I wish to use a Running Total, but using a date from a related table.

Running Total = 
CALCULATE(
    SUM('CostTable'[Amount]),
    FILTER(
        ALLSELECTED(DATES[Date]),
        ISONORAFTER(DATES[Date], MAX(DATES[Date]), DESC)
    )
)

With the page filtered to the current year, this Running Total still includes amounts from previous years.

The formula works correctly using the date within the Cost Table, but not in the related Dates table. The two tables are related on the Date field.

What am I doing wrong?

1
powerbidax

1 Answers

0
votes

It would appear that a small change in the formula has corrected the problem.

Using:

Running Total = 
    CALCULATE(
        SUM('CostTAble'[Amount]),
        FILTER(
            ALLSELECTED(DATES),
            ISONORAFTER(DATES[Date], MAX(DATES[Date]), DESC)
        )
    )

The difference being that ALLSELECTED specified only the table, and not the field within the table.

Use: ALLSELECTED(DATES) Not: ALLSELECTED(DATES[Date])