LYTD formula for DAX

Power BI
1

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

2

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
[Enterprise%20DNA%20Expert%20-%20Small]

3

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

4

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?

5

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
image

6

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?

7

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

Year =
YEAR ( 'Date'[Date] )

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

Thanks
image