DAX What is SAMEPERIODLASTYEAR actually doing with the date range

[MarkCBB]

Hi there,

I have a question about what the below Dax function is actually doing with the date range. 

The below DAX Function is the function for same period last year, but if the date is the 21st of this month, is the below function measuring from the 1st to the 21st or the 1st to the 31st (Full month).

LYTM Value = CALCULATE([Total Value],SAMEPERIODLASTYEAR(DATESMTD('CALENDAR'[DATE])))

This is the Sum

Total Value = SUM(SALES[VALUE])

This is the This year this month calc

TYTM Value = CALCULATE([Total Value],DATESMTD('CALENDAR'[DATE]))

This is the main measure, this shows negitive for most of the month, until it gets later into the month thus me questioning if the LYTM calculation is to the date last year or to the full month of the date this year - Hope that makes sense.

TYLM Growth Value = DIVIDE(([TYTM Value]-[LYTM Value]),[LYTM Value],0)

What I want the LYTM function to do, is go up until the date of the TYTM function.

[Greg_Deckler]

https://msdn.microsoft.com/en-us/library/ee634972.aspx

The dates returned are the same as the dates returned by this equivalent formula:

DATEADD(dates, -1, year)

[MarkCBB]

Hi @Greg_Deckler I have create a small table to test the results, the table has 2 columns, Date, Sales. Dates start from 2015-01-01 and end at 2016-07-24. Each date has a Sales value of 1.

The Ytd function returns 206, so 206th date. however the Last ytd function returns 212, so the dates go past the 2016-07-24.

Should the function not return that same value?

[OwenAuger]

This appears to be one of the quirks of time-shifting time intelligence functions, and a good example of why your Date table should include complete months and years.

SAMEPERIODLASTYEAR (and DATEADD) apply special logic when shifting the last day of a particular month, to ensure it shifts to the last day of the same month in the prior year (or month in the case of DATEADD used with months).

In your case, SAMEPERIODLASTYEAR recognises that 2016-07-24 is the last day in July 2016 in your Date table, so when it shifts back one year to 2015, it deems the corresponding date to be 2015-07-31 (the last day of July in 2015 in your Date table).

This is more useful for cases when months have unequal numbers of days (e.g. Feb 28/29 when shifting by a year, or 30/31 day months when using DATEADD).

This is obviously not what you intended! Whether you call it a feature or a bug,extending your Date table to cover full months/years should solve it.

Owen 🙂

[MarkCBB]

@OwenAuger and @Greg_Deckler 

There is something very basic that I am not understanding here, Below is a link to download my pbix file, in the file there are 2 tables, DATA and DATES. 

In the DATA table there are 2 columns, Date and Sales, in teh DATE table there is 1 column Date.

I have added in 3 measures, but I am not geting the results that I am expecting and I have no idea why, 

Would either of you mind have a look at it and let me know where I am going wrong. 

What I would like is for the sales to match, i.e. This YTD should SUM from the 2016-01-01 to the 2016-07-24 and the the Last YTD sould then SUM the 2015-01-01 to the 2015-07-24. 

PBI FIle

Excel File

[OwenAuger · Accepted solution]

Hi @MarkCBB,

Thanks for attaching your model 🙂

I have uploaded a file with some suggested changes.
https://www.dropbox.com/s/zhxslmbe8xy63l0/DateTesting%20V2.pbix?dl=1

I think the V3 measures in the above file do what you want, but have a play with them yourself.

This YTD V3 (blank after max date in DATA) = 
IF (
    MIN ( DATES[DATE] ) <= CALCULATE ( MAX ( DATA[DATE] ), ALL ( DATA ) ),
    CALCULATE ( [Total Sales], DATESYTD ( DATES[DATE] ) )
)

Last YTD V3 (blank after max date in DATA) = 
IF (
    MIN ( DATES[DATE] ) <= CALCULATE ( MAX ( DATA[DATE] ), ALL ( DATA ) ),
    CALCULATE (
        [Total Sales],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                DATESYTD ( DATES[DATE] ),
                DATESBETWEEN (
                    DATES[DATE],
                    BLANK (),
                    CALCULATE ( MAX ( DATA[DATE] ), ALL ( DATA ) )
                )
            )
        )
    )
)

Explanation:

1. Both of [This YTD] and [Last YTD] were referring to DATA[DATE]. Time intelligence functions should normally refer to the date column of your date table rather than the fact table, i.e. should refer to DATES[DATE] in your case. This is because there can be issues with non-contiguous date ranges, and time intelligence functions can only return values present in the column they are given, so you would run into problems if there were dates without sales.
   I've corrected this in the V2 measures. (Also [Last YTD] was missing a call to DATESYTD.)
2. The second issue is that I think you want to prevent displaying [This YTD] and [Last YTD] beyond dates actually present in the DATA table. I've done this in the V3 measures.
   
   • I borrowed a technique from here (http://www.daxpatterns.com/cumulative-total/) to check if the min currently filtered date from DATES is <= the global max date from DATA. This is good enough for [This YTD]
   • For [Last YTD] you also need to ensure that the dates shifted back one year don't go beyond the global max date. I did this by intersecting DATESYTD(...) with DATESBETWEEN(BLANK(), global max DATA[DATE]). This was a bit fiddly and there could be better ways of doing this.

Owen 🙂

Owen Auger, CFA

https://nz.linkedin.com/in/owenauger

[Greg_Deckler]

I'm sorry, what functions are you referring to when you say "Ytd" and "Last ytd"?