topic Re: Dynamic LAST YEARTODAY in DAX Commands and Tips

Dynamic LAST YEARTODAY

Anonymous — Thu, 10 Oct 2024 13:37:33 GMT

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.

Re: Dynamic LAST YEARTODAY

lbendlin — Thu, 10 Oct 2024 15:01:11 GMT

Add a filter

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

Re: Dynamic LAST YEARTODAY

Anonymous — Thu, 17 Oct 2024 09:15:49 GMT

Hi Anonymous ,

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