Quick Measure Running Total - filter to stop line at today

[LaurenceSD]

Hi,

I've used a quick measure to calculate a running total for This year, last year, the year before and my Budget, by month, all is working fine except I can't seem to get a filter in there that'll stop the line for this year going past today

Here is the formula

Enqs FY21 Cuml =

CALCULATE(

    [Enqs FY21],

    FILTER(

        ALLSELECTED('Dates'[Correct Order FiscalM]),

        ISONORAFTER('Dates'[Correct Order FiscalM], MAX('Dates'[Correct Order FiscalM]), DESC)

    ))

& this is what it produces

I've tried to add in a filter on todays date, but when i do that i'm then seeing the cumulative stop

Enqs FY21 Cuml =

CALCULATE(

    [Enqs FY21],

    FILTER(

        ALLSELECTED('Dates'[Correct Order FiscalM]),

        ISONORAFTER('Dates'[Correct Order FiscalM], MAX('Dates'[Correct Order FiscalM]), DESC)

    ),FILTER(dates,Dates[Dates]<=today()))

 It doesn't seem to matter where I put the today filter I'm getting the same results

any suggestions are appreciated

Thanks

[amitchandak]

@LaurenceSD , Try like

Enqs FY21 Cuml =
CALCULATE(
[Enqs FY21],
FILTER(
ALLSELECTED('Dates'[Correct Order FiscalM]),
ISONORAFTER('Dates'[Correct Order FiscalM], MAX('Dates'[Correct Order FiscalM]), DESC) && 'Dates'[Correct Order FiscalM] <=today()
))

Filter should be on the date you have taken allselected , I think so

[LaurenceSD]

Thanks @amitchandak  but that still produces the same result as the first graph

[amitchandak]

@LaurenceSD ,is month or date in visual coming from date table if yes try like

Enqs FY21 Cuml =
CALCULATE(
[Enqs FY21],
FILTER(
ALLSELECTED('Dates'),
ISONORAFTER('Dates'[Dates], MAX('Dates'[Dates]), DESC) && 'Dates'[Dates] <=today()
))

or

Enqs FY21 Cuml =
if( max('Dates'[Dates]) <=today(),
CALCULATE(
[Enqs FY21],
FILTER(
ALLSELECTED('Dates'),
ISONORAFTER('Dates'[Dates], MAX('Dates'[Dates]), DESC)
)), blank())

[LaurenceSD]

Thanks, @amitchandak  I do have a date table in place, but I think there is something up with my model, potentially due to the size, that it keeps bugging out if I try to use the Date column instead of the month - any visual i use has the dots circling to say its refreshing, but it never actually does refresh (I'd originally done my cumulative values based on the date column), I think it's to do with the size of my model and some inefficient formulas (was done soon after learning some Dax). Your formulas look like they should work, I'll do some more digging my end. Thanks for your help

[v-lionel-msft]

Hi @LaurenceSD ,

It may also be related to the date field used by your X-axis.
If the problem persists, it is best to provide a sample .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.