Forum Discussion

LaurenceSD's avatar
LaurenceSD
Icon for Advocate II rankAdvocate II
5 years ago
Solved

Running total - stop at today

Hi,   I've managed to create a running/cumulative total for my bookings, however, I'm trying but failing to find a way of stopping this year's line carrying on past today's date.   My Dax formula...
Need Help
  • LaurenceSD's avatar
    LaurenceSD
    5 years ago

    a slicer of dumb luck led me to the correct formula, when playing about with some other ideas I accidentally left out the .date from my date table and voila it worked - I'm guessing something to do with the way time intelligence works (i'd not marked my date table as a table, as I wanted to be able to use the predefined heirachy)

     

    So this

    CALCULATE(BOOKINGS[Bookings],DATESYTD('DATESBKGS&ENQS'[Dates-Bkg&Enq].[Date]))

    becomes

    CALCULATE(BOOKINGS[Bookings],DATESYTD('DATESBKGS&ENQS'[Dates-Bkg&Enq])

    & it works strangely (or not so strangely I'm sure to some!)

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi LaurenceSD ,

 

Try to do like this.

Or please use this formula.

Measure = 
VAR x = 
CALCULATE(
    SUM(Sheet9[Value]),
    DATESYTD(Sheet9[Date])
)
RETURN
IF(
    MAX(Sheet9[Date]) > TODAY(),
    BLANK(), x
)

 

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.

 

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

v-lionel-msft thanks for the suggestions, neither option seems to be working for me right now, not sure if it's because I'm using a Distinctcount formula to calculate my number of bookings?