 # LYTD formula for DAX

For some time I have used the following formula for YTD and LYTD Sales data.
`

YTD_Sales = CALCULATE([Tot_Sales],filter(Amer_Date,Amer_Date[Date] >= MIN(Amer_Date[Date])),filter(Amer_Date,Amer_Date[Date] <= Amer_Date[Date]))

This works fine, the issue I run into is with LYTD. I have tried every solution I could find on Power BI forums. Most return all of last year and not a true LYTD. The closest I got to a working formula is :

LYTD_Sales =
IF(
LASTNONBLANK(Amer_Date[Date],[Tot_Sales])>MIN(Amer_Date[Date]),
CALCULATE([Tot_Sales],SAMEPERIODLASTYEAR(DATESBETWEEN(Amer_Date[Date],MIN(Amer_Date[Date]),LASTNONBLANK(Amer_Date[Date],[Tot_Sales])))))

The problem I discovered is if a customer did not order anything in a while his LYTD sales only shows Sales up to the last date for the current year and that is not a correct Year to Year comparison
What I need is a true comparison for this year today to today a year ago.
It would be great if someone knows how to solve this issue that I figured should be very common

Any reason why you are not using a Date Table for either of these Measures? Here are measures I use for both:
REV YTD =
CALCULATE ( [REV], DATESYTD ( ‘Date’[Date] ) )

``````For PYTD I use the Year & Day of Year Columns to help create a calculated column called PYTD.
PYTD Calculated Column=
IF (
YEAR ( TODAY () ) - 1 = [Year]
&& [Day of Year] <= [Todays Day Of Year],
TRUE,
FALSE
)

Now You can create your PYTD Measure with the PYTD Calculated Column:
REV PYTD =
CALCULATE ( [REV], 'Date'[PYTD] = TRUE )
``````

Thanks
[ ]

Thanks, I will try this out and yes, I am using a Date Calendar Table called ‘Amer_Date’

I tried it and I am getting no values returned at all. These are the formula’s I used after I added the TRUE/FALSE column to my Calendar Date table

`YTD_2 = CALCULATE([Tot_Sales],DATESYTD(Amer_Date[Date]))`

`LYTD_Sales9 = CALCULATE([YTD_2],Amer_Date[LYTD]=TRUE)`

What did I miss?

I forgot to give you the measure called " Todays Day of Year". That was part of the PYTD calculation.

``````Todays Day Of Year =
CALCULATE ( MAX ( 'Date'[Day of Year] ), 'Date'[Date] = TODAY () )
``````

Thanks Ok, in that case are [Year] and [Day of Year] also measures I need to create since they don’t refer to the Date Table?

Yes, I have both as calculated columns stored in my Date table.

``````Year =
YEAR ( 'Date'[Date] )

Day of Year =
FORMAT ( 'Date'[Date], "y" )
``````

Thanks 