YTD and YTD Previous Year (a struggle)

[PowerRon]

Hi,

I want to create a KPI in my report that takes the max date from my Sales table and then compares the current year-to-date with the year-to-date of the same period previous year.
So, if we are now living on 2024-04-24, I would like to have the values:

- YTD 2024-01-01 up until 2024-04-24

- YTD previous year 2023-01-01 up until 2023-04-24

For YTD I created this, which works

Sum Quantity YTD =

VAR _maxdate = MAX('Sales'[OrderDate])

RETURN

TOTALYTD([Sum Quantity],'Date'[Date],'Date'[Date] <= _maxdate)

For YTD previous year I created this

Sum Quantity PYTD =

VAR _maxdate = MAX('Sales'[OrderDate])

VAR _lastyear = YEAR(_maxdate)-1

VAR _maxdate_pr = DATE(_lastyear,MONTH(_maxdate), DAY(_maxdate))

RETURN

TOTALYTD([Sum Quantity],'Date'[Date],'Date'[Date] <= _maxdate_pr)

But I can't get it working. I get an error saying :
An argument of function 'DATE' has the wrong data type or the result is too large or too small.

I hope someone can help. Tried many things but can't get this Sum Quantity PYTD working.

Thnx a lot 
Ron

@parry2k 

[PowerRon]

Thnx Parvinder, yours also works. 
Always glad you give me the push in the right direction

[parry2k · Accepted solution]

@PowerRon this is the measure, it is well explained in my videos:

PYTD Sales = 
VAR __Date = MAX ( 'Sales'[OrderDate] )
VAR __LastYear= YEAR ( __Date ) - 1
VAR __PDate = IF ( __LastYear <> - 1,  DATE ( __LastYear, MONTH ( __Date ), DAY ( __Date ) ) )
RETURN
TOTALYTD ( [Base Sum Sales], CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] <= __PDate  ) )

[PowerRon]

Hi @parry2k I have a date dimension and it is marked as a date table. Bit still couldn't get it working.

Looks like this works

Sum Quantity PYTD =

VAR DataMaxDate =

    CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) )

RETURN

    CALCULATE (

        [Sum Quantity YTD],

        SAMEPERIODLASTYEAR ( DATESBETWEEN ( 'Date'[Date], BLANK (), DataMaxDate ) )

    )

[parry2k]

@PowerRon 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

Check this video and the same logic can be used for the previous year:

Learn how to compare MTD sales as of date with same number of days last year - Part 7 (youtube.com)

Power BI Time Intelligence: Compare MTD With Same Period In Previous Months - Part 6 - YouTube