Total Sales up to a point in time (NOT TOTALYTD)

[IrishChristof]

Hi all,

I have been scouring the internet trying to find a solution to my problem, but as yet cannot find one.

Basically I was wondering if there was any DAX that would calculate (in the below example) total Passengers up to 10th May rather than just the 10th of May.

But I dont want to use a formula like this... 

=CALCULATE(TOTALYTD([Passengers],DatesList1[Date]),DatesList1[Date]=today())

...as that will only give me Passengers booked between 1st of Jan and 10th of May.

Then for the second part, I'd also like to see Passengers this time last year.

Again, the YTD formula wont work 

=CALCULATE(TOTALYTD([Passengers],DatesList1[Date]),DatesList1[Date]=(today()-365))

as it will only give me 1st of Jan to 10th May last year.

So I guess I need something like DATESBETWEEN formula maybe, but I can't seem to figure them out. 

Any help would be greatly appreciated 🙂

[parry2k]

@IrishChristof this will do it

Sales PY = 
TOTALYTD(
    [Passengers], 
    DATEADD( 
        CALCULATETABLE(
            VALUES(DateList1[Date]),
            KEEPFILTERS( DateList1[Date] <= TODAY() )
        ), 
        -1,
        YEAR 
    ) 
)

[IrishChristof]

Thanks for the reply, but this is still giving a Jan 1st - May 10th result.

What i want to see is anything from day0 (eg: 1st Jan 1900) through to the day selected on the slider (in this case 10th May 2019).

Then another DAX that will tell me, up to the same time last year.. eg: Day0 through to 10th May 2018 in this case

The YTD formulas are great, but not for this particular task.

Thanks again

Chris

[parry2k · Accepted solution]

@IrishChristof  here are the new measures

RT TY = CALCULATE( [Total Sales], FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] <= TODAY() ) ) 

 RT PY = 
 VAR __maxDate = CALCULATE( MAX( 'Calendar'[Date] ) ,
 DATEADD( 
        CALCULATETABLE(
            VALUES('Calendar'[Date]),
            KEEPFILTERS( 'Calendar'[Date] <= TODAY() )
        ), 
        -1,
        YEAR 
    ) 
)
RETURN 
CALCULATE( [Total Sales], FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] <= __maxDate ) ) 

[IrishChristof]

This works beautifully.

Thank you so much!! 🙂