Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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.
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
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
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
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?
All your date hierachies should come from the date table.
For example, my date table looks like this
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |