Forum Discussion

LaurenceSD's avatar
LaurenceSD
Icon for Advocate II rankAdvocate II
2 years ago

Running Total - Resets with Date Filter

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

Need Help

3 Replies

  • LaurenceSD's avatar
    LaurenceSD
    Icon for Advocate II rankAdvocate II
    2 years ago

    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

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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 🫡👍