Running Total - Resets with Date Filter

[LaurenceSD]

Hi,

I've got a formula for calculating a running total which is this

Bookings This Year Running Total =

if(max(Dates[Date])<=today(),

CALCULATE(

    [Bookings This Year],

    FILTER(

        ALLSELECTED('Dates'[Date]),

        ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)

    )

),BLANK())

The problem I've got is when I use a date slicer, the count doesn't reset, is there a way I can modify this formula, so that when i select a FY and a month, that the start value rests to 0, so in the below example, instead of 1st Jan 2024 representing the 39,134th booking ever, it would said say 2 & 23rd January 2024 would say 458

The dataset used in this report is massive, so hard for me to upload, but if needed I can try and create a smaller replica

Thanks

[Daniel29195]

hello @LaurenceSD 

modification to your code : 

Bookings This Year Running Total =

var min_date =  calculate(min(dates[date]),  allselected(dates[date]))

var max_date = max(dates[date])

if(max(Dates[Date])<=today(),

CALCULATE(

    [Bookings This Year],

all(dates[date]) ,

  dates[date] <=max_date  , 

dates[date] > = min_date
)

),BLANK())

let me know if you need any other help

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[LaurenceSD]

Bookings This Year is simply a countrows formula, labeled as This Year for ease (I then have LY, LY-1, LY-2, etc coming off with SAMEPERIODLASTYEAR) but nothing clever going on with it.

I'll check out the video looks useful, thanks

[Greg_Deckler]

@LaurenceSD What is the formula for Bookings This Year? You could give BRT a shot. 

Better Running Total - Microsoft Fabric Community

At least that way you could actually troubleshoot what was going on.