SAMEPERIODLASTYEAR not displaying dimensions with no values in current year

[Maikeru]

Dear all,

I have a simple dataset of sales volume by customer accross multiple years.

When doing a Year-over-Year comparison by customer using SAMEPERIODLASTYEAR, PowerBI leaves out the rows which have no values in current year (in the below example customer C5)

Volume LastPeriod = CALCULATE(
    [Volume CurrentPeriod],
    SAMEPERIODLASTYEAR(Sales[Date])
)

The total is well calculated though.

I assume this has to do with the date slicer, but isn't there a way to work around this?

Best regards

Michael 

[AllisonKennedy]

You definitely need to use a DimDate table and you will solve this problem.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[Maikeru]

@amitchandak @AllisonKennedy 

Many thanks, this fixed the issue!

I usually use a separate calendar table, but this time I tried to simplify my model thinking the output would be the same.

I was wrong. Lesson learnt!

[amitchandak · Accepted solution]

@Maikeru , Please use date table for that.

Volume LastPeriod = CALCULATE(
    [Volume CurrentPeriod],
    SAMEPERIODLASTYEAR(Date[Date])
)

Refer

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[AntrikshSharma]

C2 and C3 do not have any values in 2019 so that is the correct behavior.