Dynamic LAST YEARTODAY

[camiurrego0]

Hi everyone,

I am using two measures to sum up the amount of sales YTD (year to date) for current vs last year.

For example: Today is 10.10.24, so:

2024: sales from 01.01.24 to 10.10.24

2023: sales from 01.01.23 to 10.10.23 

This is working perfectly when it is about 2024 (current year) vs 2023. But I also need to replicate the results for let's say 2023 vs 2022, 2022 vs 2021. 

YTD_PartnerasTK = TOTALYTD(SUM('Dataset'[Sales]), 'Dim Datum'[Datum],  'Dim Datum'[Datum] <= TODAY()

 

PYTD_PartnerasTK = CALCULATE(SUM('Dataset'[Sales ]),

                            CALCULATETABLE(SAMEPERIODLASTYEAR(DATESYTD('Dim Datum'[Datum])), 'Dim Datum'[Datum] <= TODAY()

                            ))

The reason why I am using today() function here is because I have wrong date in my dataset for the future (sales in december 2024 for example) and I don't want to include this wrong data. But at the same time, this today() is an obstacle for calculating dynamically the YTD and YTD for the last year. I already tried replacing the TODAY() with a MAX('Dim Datum'[Datum]) function but it is not working. 

Do you have any tip that doesn't involve changing radically my current measures? 

I have tried a lot of methods but it is not working even for 2024 vs 2023. 

[Anonymous · Accepted solution]

Hi @camiurrego0 ,

Do you want to show the total sales from the beginning of last year to this day last year? Here is my method for your reference.

LastYear_To_Today = 
VAR last_today = FORMAT(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),"MM/DD/YYYY")
VAR current_year = YEAR(MAX('Table'[Date]))
VAR LastYear_To_Today =
    CALCULATE (
        SUM ( 'Table'[Sale] ),
        FILTER (
            ALL('Table'),
            FORMAT('Table'[Date],"MM/DD/YYYY") <= last_today &&
            YEAR('Table'[Date])= current_year - 1
        )
    )
RETURN
    IF( ISBLANK(LastYear_To_Today), 0, LastYear_To_Today )

Best regards,

Mengmeng Li

[lbendlin]

Add a filter 

FORMAT(MAX('Dim Datum'[Datum]),"MMDD")<=FORMAT(TODAY(),"MMDD")