YTD last year DAX

[PavelR]

Hi,

I have question about YTD Last year DAX formula. Even though I have read many blogs and topics here in community, I still don't know how to solve this issue.

Suppose I have data like below.

ID	Date	Amount
1	01.01.2016	10
2	02.01.2016	15
3	03.01.2016	11
4	10.01.2016	8
5	20.02.2016	7
6	01.01.2017	10
7	02.01.2017	12
8	03.01.2017	9

I would like to figure up YTD value of amount for actual data (year 2017) and then YTD value for corresponding period previous year.

For YTD I use formula:

YTD = CALCULATE(SUM(Data[Amount]);DATESYTD(Date[Date]))

For YTD LY I use formula:

YTD LY = CALCULATE([YTD];SAMEPERIODLASTYEAR(Date[Date]))

Then when I use these formulas in PBI, I have got incorrect value for YTD LY. I expect value 36 (dates: 1.1. - 3.1. 2016), but in total I have got 44 (it also calculates Amount 8 in 10.1.2016) - so it gives me period of january 2016, but I want only the corresponding days from 2017.

But YTD LY value in pic below on row for 3.1.2017 is OK.

Does anybody know the solution?

Thanks.

Regards.

Pavel

[OwenAuger]

@ToddChitt

Got it...the reason your particular measure is not straightforward is that the built in time intelligence functions are designed to perform calculations relative to the selection on the Date table rather than TODAY().

There are multiple ways you could handle this - here is one possibility (code below).

The logic I have used is to

1. Get the month/day of TODAY()
2. Shift that into the currently filtered year (based on the maximum selected date)
3. Create two date filters: YTD based on the shifted date in step 2 & YTD based on the maximum selected date
4. Calculate Sales with Date filtered to the intersection of the filters from step 3

The reason for the intersection in step 4 is to handle cases where you are filtering at a lower level than year. So if today is 25-Aug-2017 and you have filtered on Jul-2017 in a visual, the measure would return sales from 1-Jan-2017 to 31-Jul-2017. However if you selected any month from Aug-2017 to Dec-2017, you would get sales from 1-Jan-2017 to 25-Aug-2017.

Sales Amount YTD based on current date = 
VAR Today =
    TODAY ()
VAR TodayMonth =
    MONTH ( Today )
VAR TodayDay =
    DAY ( Today )
VAR MaxDateSelectedYear =
    YEAR ( MAX ( 'Date'[Date] ) )
VAR TodayShiftedToSelectedYear =
    DATE ( MaxDateSelectedYear, TodayMonth, TodayDay )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE (
            DATESYTD ( 'Date'[Date] ),
            'Date'[Date] = TodayShiftedToSelectedYear
        )
    )

Regards,

Owen 🙂

[Robert_Jensen]

Hi Owen,

Is it possible to get the logic below with the exception that it calcultates sales amount with a start date based on selected value from a date slicer?

Sales Amount YTD based on current date = 
VAR Today =
    TODAY ()
VAR TodayMonth =
    MONTH ( Today )
VAR TodayDay =
    DAY ( Today )
VAR MaxDateSelectedYear =
    YEAR ( MAX ( 'Date'[Date] ) )
VAR TodayShiftedToSelectedYear =
    DATE ( MaxDateSelectedYear, TodayMonth, TodayDay )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE (
            DATESYTD ( 'Date'[Date] ),
            'Date'[Date] = TodayShiftedToSelectedYear
        )
    )

At the moment it always return YTD from 01-Jan to max TODAY or to a selected date less than TODAY. But when selecting the date range with start 10-Jan-19 to 17-Jan-19 it still returns the sales amount for 01-Jan-19 to 17-Jan-19. 

Regards,

Robert

[Anonymous]

Hi @Robert_Jensen,

That mean you are not after really YTD rather data between selected dates. If you select full year, you want actually YTD data and if you select some mid range of dates, you want data for those dates only, hence aggrigation for selected dates and not YTD. 

In that case, don't use DATESYTD because this will always start from first day of selected year. May be something like below:

Sales Amount YTD based on current date =
CALCULATE (
    [Sales Amount],
    FILTER ( 'Date', 'Date'[Date] >= MIN ( 'Date'[Date] ) ),
    FILTER ( 'Date', 'Date'[Date] <= 'Date'[Date] )
)

emudria

[MarkusDW14]

I used the update for YTD to 

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 soulution I could find. Most return all if 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

[therealomacoder]

For those of you who have gone through all the formulas on the 5 pages so far on this blog and still haven't found a working solution for you....

I FINALLY found one that works for my data set from here: https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/

Oye!

Here's the formula that ended up working for me:

CALCULATE([Total Sales],
SAMEPERIODLASTYEAR(DATESBETWEEN(Calendar[date], STARTOFYEAR(Calendar[Date] ),
LASTNONBLANK(Calendar[Date], [Total Sales]))))

[Robert_Jensen]

Thank you very much @Anonymous!

That worked exactly as I wanted it to. How would you create a LY-measure to behave accordingly for the same period last year? Assume it's a slight change in the definition of date filtering.

I tried this formula but it doesn't set a last date to maximum TODAY -1 year.

Sales Amount LY  = CALCULATE([Sales Amount YTD based on current date];SAMEPERIODLASTYEAR('Date'[Date]))

Your help is much appreciated!

Regards,

Robert

[Anonymous]

What are you getting from 

Sales Amount LY  = CALCULATE([Sales Amount YTD based on current date];SAMEPERIODLASTYEAR('Date'[Date]))

[Robert_Jensen]

It works fine as long as I don't extend my date filter to more than TODAY. 

If I for example set the date filter to 1-jan-19 to 31-dec-19 the Sales Amount LY measure will sum upp sales for 1-jan-18 to 31-dec-18. The behaviour I'm looking for is that it in this case only calculate for 1-jan-18 to 13-feb-19 (today - 1 year as max).

Thought that if I use a YTD measure the way you fomulated it, I would do exactly that.. 🙂

So my problem is really that I would like a LY-measure that use MIN selected day -1 year and then as TODAY -1 year as MAX selected value.

Regards,

Robert

[Anonymous]

Hi @OwenAuger 

Thanks for your tips on working with YTD LY formulas, they were very helpful for me. Currently I'm struggling with the following issue. I have ar report with a single Year Slicer, data is loaded with one month back period - there is a DATEADD formula which substracts 1 month from YTD LY to match the data.

I have a YTD and YTD LY measures, so for month 04.2019 they will show following data:

- 2019 YTD - sum of months 1,2,3 2019

- YTD LY - sum of months 1,2,3 2018

But when I change year slicer to show the last year data, which should be the full year data, DATEADD formula substracts -1 from months, so:

- 2018 YTD I have sum of all months 1 - 12 2018

- YTD LY - it shows data 11 months, 1 - 11 2017

I've tried to apply some IF formula to solve that using Calendar table, but I can't use calendar table for logical test.

Here is my YTD LY formula for this report:

SomeIndicator YTD LY = 
VAR DataMaxDate =
    CALCULATE ( MAX ( kalendarz[Date].[Date] ); ALL ( kalendarz[Date] ) )
RETURN
    CALCULATE (
        [ SomeIndicator YTD];
        SAMEPERIODLASTYEAR (
            DATEADD(
            INTERSECT (
                VALUES ( kalendarz[Date].[Date] );
                DATESBETWEEN ( kalendarz[Date].[Date]; BLANK (); TODAY() )
            ); -1;MONTH)
        )
    )

[OwenAuger]

@Anonymous 

Just clarifying your requirements...

Is this the behaviour you want?

1. When you filter on a year that is entirely in the past (i.e. 2018 or earlier as at today), you want to filter on the entire year for both the YTD and YTD LY measures?
2. When you filter on the current year (i.e. 2019 as at today), you want to adjust the YTD and YTD LY filters so include only months up to the previous month based on today's date (i.e. up to March if today is in April).

[Anonymous]

@OwenAuger 

1. When you filter on a year that is entirely in the past (i.e. 2018 or earlier as at today), you want to filter on the entire year for both the YTD and YTD LY measures?
2. When you filter on the current year (i.e. 2019 as at today), you want to adjust the YTD and YTD LY filters so include only months up to the previous month based on today's date (i.e. up to March if today is in April).

---

1. Yes, e.g. when I filter on year 2018, I want to YTD measure show data for entire 2018, and YTD LY to show entire 2017.
2. That's right, when filter is set to current year, I want to include only YTD data up to previous Month for current and past year. E.g now is April 2019,  then YTD = 01,02,03 2019; YTD LY = 01,02,03 2018.

And, maybe it is important too, I have data in fact table by moth-year. So it is connected to Calendar Table by month-year key.

[OwenAuger]

Hi again @Anonymous 

I tinkered around with this one a bit.

Below are some measures that I believe should do what you want.

One note - I recommend you mark your kalendarz table as a Date Table, so you don't have to use the automatically generated date hierarchies with the dot-notation. The below measures assume you've done this.

The logic is essentially to limit the YTD date filter to only those dates up to and including the end of last month, using the INTERSECT function.

The IF(...) also ensures that the measures don't return a result for dates after the end of last month.

SomeIndicator YTD =
VAR EndOfLastMonth =
    EOMONTH ( TODAY (); -1 )
RETURN
    IF (
        MAX ( kalendarz[Date] ) <= EndOfLastMonth;
        VAR DateFilterYTD =
            INTERSECT (
                DATESYTD ( kalendarz[Date] );
                DATESBETWEEN ( kalendarz[Date]; BLANK (); EndOfLastMonth )
            )
        RETURN
            CALCULATE ( [SomeIndicator]; DateFilterYTD )
    )

SomeIndicator YTD LY =
VAR EndOfLastMonth =
    EOMONTH ( TODAY (); -1 )
RETURN
    IF (
        MAX ( kalendarz[Date] ) <= EndOfLastMonth;
        VAR DateFilterYTD =
            INTERSECT (
                DATESYTD ( kalendarz[Date] );
                DATESBETWEEN ( kalendarz[Date]; BLANK (); EndOfLastMonth )
            )
        VAR DateFilterYTD_LY =
            SAMEPERIODLASTYEAR ( DateFilterYTD )
        RETURN
            CALCULATE ( [SomeIndicator]; DateFilterYTD_LY )
    )

Let me know if this works for you.

Regards,

Owen

[Anonymous]

Hello @OwenAuger ,

Many thanks for your effort so far.

LY YTD measure works fine but only if I use months. For entire years, measeure is calculated only for past years, for current year is blank. And I need it to display for full year.

I've created pbix file with some random sample data for this case. Date is still as [Date].[Date] because my PowerBi app crashes after marking table as date table:)

LY YTD sample data

[OwenAuger]

@Anonymous Thanks for testing this out 🙂

A small tweak is required to my original suggestion - thanks for spotting!

Uploaded corrected version of your file here.

The part of both measures that tests whether the Max Date is <= EndOfLastMonth should actually test whether Min Date is <= EndOfLastMonth. i.e. it should be changed to

IF ( MIN ( 'Calendar'[Date].[Date]) <= EndOfLastMonth

With this change, you will see the expected result for years that are partially complete. The logic is that you want to return a result as long as the filtered date range begins on or before EndOfLastMonth. 

Regards,

Owen

[Anonymous]

@OwenAuger big thank you! 🙂 It works great!

[Anonymous]

@OwenAuger

I tried to use the measure you created below to solve a similar problem I am having. My issue though is that I'd only like YTD through the end of the previous month. So not including any of current month revenues. How would I modify that to provide YTD through the previous month for current year and previous years? I'm still very beginner and I've never used VAR before, so I was trying to figure out how to modify that part but can't figure it out. 

[OwenAuger]

@Anonymous

Sure - the way I would do it is by changing the  TodayShiftedToSelectedYear to be the end of the previous month.

This will mean that if TODAY() is in January, you will get a blank measure, and in February you will just see January etc.

Sales Amount YTD based on current date = 
VAR Today =
    TODAY ()
VAR TodayMonth =
    MONTH ( Today )
VAR TodayDay =
    DAY ( Today )
VAR MaxDateSelectedYear =
    YEAR ( MAX ( 'Date'[Date] ) )
VAR TodayShiftedToSelectedYear =
    EOMONTH ( DATE ( MaxDateSelectedYear, TodayMonth, TodayDay ), -1)
RETURN
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE (
            DATESYTD ( 'Date'[Date] ),
            'Date'[Date] = TodayShiftedToSelectedYear
        )
    )

Could you try this out and post back if you need more help?

Regards,

Owen

[Anonymous]

@OwenAuger

That worked!! Thank you SO much. Sadly, I think I spent 2 days working on this and still couldn't figure it out.

[jwjwjwjwjw]

Hello Owen, thanks for all the work on this thread. It has helped me immensely already. Your solution in the previous post works "almost" works for me, except for one error that I can't even begin to imagine the source of. 

My data has 6 years, and the "to date" shows up as it should for every year except for 2016. And 2016 is just blank. This isn't a problem with my data, as 2016 shows up just fine in all other visuals/scenarios. Does anything come to mind as to what could be going on?

EDIT: I jumped the gun replying here, I think today's date in 2016 was over the weekend and didn't have any data, but adding the that date via a calendar table with all dates seems to have fixed it. 

Thanks again, Owen! 

[rajibmahmud]

@OwenAuger

Thanks. This helped me a lot.

[PavelR]

Awesome @OwenAuger, thanks a lot!

I really appreciate your effort. I will try it implement to my PBI file.

Could you also explain to me please, why the function SAMEPERIODLASTYEAR doesn't do that comparison as default behavior? I mean the comparison of the corresponding previous period? Why it compute YTD LY as the whole month (value 44)?

I think that my requirement ("business case") is quite standard, but the solution is not so easy 

Regards.

Pavel