SAMEPERIODLASTYEAR Must you use the Date hierarchy in a visual with this measure in?

[DebbieE]

I just want to understand the measures with SAMEPERIODLASTYEAR

If you use this in a visual, must you always have the DateTime Hierarchy in the visual? You cant for example change to use Month Year column from my date table?

I am asking this because when I swap to Year Month The Measure Containing SAMEPERIODLASTYEAR becomes identical to the measure that I show it against

So I am looking for a Rule here. Must it always be used with the Date Hierarchy?

[Anonymous]

Hi, 

The Month Year column you have is a column from the raw data? Or is it from the Date Table that you have? 

Per my understanding, SameperiodLastyear function, takes a column which is in the Date format only. If your Month Year is not a dateformat the results you get out of it wont be accurate.

[DebbieE]

I am going to try to mark as a date table and try that. other wise the one thing I could do is create a Max Date in the month and use that I guess. At least I now Have a couple of things to try

[speedramps · Accepted solution]

Hi Debbie

Please consider this solution and leave kudos  ......

There is no need to have a Date Hierarchy for SAMEPERIODLASTYEAR.

For example .....

Create a calendar table and mark it as a date table

Add additional columns to the calendar table for month year and quarter year,

and ensure the “sort by” option is set correctly.

Build a relationship to your fact table.

Create your base dax measure and your last year measure

Total sales quantity =

SUM(Sales[Quantity])

Total sales quantity LY =

CALCULATE([Total sales quantity],SAMEPERIODLASTYEAR('Calendar'[Date]))

Now draw a clustered column chart

With axis = month year

And values = Total sales quantity LY, Total sales quantity

Now change the axis = quarter year

Note there was no need to have a Date Hierarchy for either of these and SAMEPERIODLASTYEAR worked ok.

This is because SAMEPERIODLASTYEAR is a date intelligence function which is driven my “date” when you mark the calendar table as your date table.

see https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

[DebbieE]

Not only has it worked but i finally understand why you mark the date table as a date table

I have always worked with dates with hierarchies before so thank you

[DebbieE]

I feel like I am getting there. Thank you

"Create a calendar table and mark it as a date table"

I have a Calendar Table (Date) already but not marked as Date

If I remember correctly, If I do this, all the dates in the report lose their hierarchies? 

This is because SAMEPERIODLASTYEAR is a date intelligence function which is driven my “date” when you mark the calendar table as your date table.

Im going to attempt to do this and see what happens (I will copy my pbix file just in case)

I am guessing I also need to create hierarchies too but if its marked as a Date Table Year Month column should then work but I lose all my Date hierarchies?

[Anonymous]

All your date hierachies should come from the date table.

For example, my date table looks like this

[speedramps]

Hi again Debbie

Always wise to back up PBIX before making changes.

It is generally considered best practice to create a Calendar dimension and centralise all your date intelligence. You can create hierarchies in the Calendar and use or not use them in visuals. It is your choice.

If you dont "Create a calendar table and mark it as a date table" then Power BI automatically creates a hidden date table for every table in your model that has a date. 

You can turn this off in Power Bi desk top > Options > Data Load > Auto date/time.

The Auto date function causes the automatic date hierarchies.

See https://docs.microsoft.com/en-gb/power-bi/transform-model/desktop-auto-date-time

[Anonymous]

You are right. But you can try different combination of relationship between Datetable and the date in your data in "Model view".

When i have the cardinality as "Many to many" the hierarchy for the Date in my table remains as is. 

Not sure what works best for you. But worth a try.