DAX Last Year to Date

Question

So I know this question has been asked a few times, and I've religiously looked over different approaches, however I still don't quite understand why I'm getting an incorrect result.

Case: I have Sales Data from ~2016 -> 2019 (up until the 2/18/2019) I'm have a Measure to show me the YTD, however I'm looking for a measure for Last Years to date(the 18th in this particular circumstance).

Right now, I have this:

    Total Sales LYTD = 
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR (
        FILTER (
            VALUES ( Sales[Completed Date] ),
            Sales[Completed Date] <= MAX ( Sales[Completed Date] )
        )
    )
)

The logic to me makes sense, but I'm sure I'm missing something, has it appears it's grabbing the ENTIRE total of 2018 when in reality i'm looking for 01/01/2018 -> 2/18/2018

This is going to be dynamically uploaded with new sales data

What am I missing? Thank you so much!

OscarLar OscarLar · Accepted Answer · 2019-02-27T15:36:02

Not sure I understand your table setup so lets look at this scenario and hopefully it helps.

Suppose you have the data in two tables, Sales and Calendar, and there's a 1:* relationship between the calendar and the sales tables. Then I would write the measures like this:

SalesToDateThisYear = 
calculate(
    Sum(Sales[Sales]);
    Calendar[Year] = Year(Today())
)

and

SalesToDateLastYear =
var dateLastYear = Today() - 365
return
calculate(
    Sum(Sales[Sales]);
    Calendar[Year] = Year(dateLatsYear);
    Calendar[Date] < dateLastYear
)

The two filter arguments are combined with a logic AND. So only dates from the first of last year to today's date last year will be included.

If you want to use the SamePeriod-function you can probably write something like this

SPLY = 
calculate = 
    Sum(Sales[Sales]);
    SamePeriodLastYear(
        Filter(
            Values(Calendar[Date]);
            Calendar[Date] >= Date(year(today()); 1; 1) && Calendar[Date] < Today()
        )
    )
)

The SamePeriod-function takes a set of dates (this year) and converts them to dates last year.

Cheers

DAX Last Year to Date

1 Answers