Last date of SAMEPERIODLASTYEAR() seems to be wrong

[rudivs]

I am trying to use SAMEPERIODLASTYEAR(Dates[Date]) to calculate the comparative nett financial position at the same time the previous year. So I have a measure `Nett Position = [Total Revenue] - [Total Expenses]`, and `Nett Position LY = CALCULATE([Nett Position], SAMEPERIODLASTYEAR(Dates[Date]))`.

The problem is that it seems to calculate to the end of the month, not to the same day last year. So LASTDATE(Dates[Date)) gives me 2018/11/07, but LASTDATE(SAMEPERIODLASTYEAR(Dates[Date])) gives me 2017/11/30, which is not the same period.

Any suggestions for how I can do a time comparison that does not include the extra days to the end of the month?

[keajht31]

I was facing similar question before and found no easy solution at all. Those DAX Time intelligence function is a joke.

Using SAMESPERIODLASTYEAR or DATESMTD/TOTALMTD combine with DATEADD will include the end of the month. A solution would be to use datebetween function with date(year(today()) - 1, month(today), day(today)). But it is not a clear solution since you need to cater the year end/start scenario where it give negative/zero. And of course the DATEADD function cannot be used on a date value so you cannot use it here. You could try add the coding to check the negative/zero value but it is just so silly that some simple thing easily doable in SQL has to be more complicate in Power BI. Not so POWER I guess?

[rudivs · Accepted solution]

@keajht31

I think I figured it out from your suggestion to use datesbetween. If you use SAMEPERIODLASTYEAR(LASTDATE(Dates[Date])) you will get the actual equivalent date. So combining that with DATESBETWEEN, you can get the correct period without the issues with year end/start:

Nett Position LY = CALCULATE(
    [Nett Position],
    DATESBETWEEN(
       Dates[Date],
       SAMEPERIODLASTYEAR(FIRSTDATE(Dates[Date])),
       SAMEPERIODLASTYEAR(LASTDATE(Dates[Date]))
    )
)