Forum Discussion
SAMEPERIODLASTYEAR() function
- Anonymous9 years ago
Your formula works fine. You are just not filtering it correctly. Meaning you need to filter/select the current period you want to measure.
Try this:
1. Create a Total Sales measure: Total Sales =
Total Sales= SUM(ResellerSales_USD[SalesAmount_USD])
2. Create a Previous Year Total Sales =
Previous Year Total Sales=CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateTime[DateKey])
3. (Drag your date field into the worksheet and make it into a slicer (use slicer visualization)
4. Drag your Total Sales and Previous Total Sales fields into the worksheet as well and make them into a card visualization (It is just easier this way, or you can make it into a table visualization)
5. Now select any month in your date slicer. Pick 3/1/2017. You will see that the Total Sales card shows 27 and the Pevious Year Sales card shows 15. You can even select 3/1, 2/1 and 1/1/2017 and it will show you previous year total Jan-March.
So you can see it is working, you need to be able to filter it to your liking. Hopefully this helps you get started. The next steps you will need is to create a measure calculation that incorporates a filter such as TOTALYTD.
"Another tip, I highly recommend NOT USING A DATE TABLE. "
I have to disagree with you there and almost always use a date table except for the most simple and basic data models.
Apart from providing a central place to filter multiple fact tables, the groupings in the PBI default table are the bare minimum. I always extend with useful columns like
- Days from Today
- Weeks from Today
- Months from Today
- Custom Date Ranges
- Week Starting
- Fiscal year groupings
- Q&A optimised columns eg. "Today", " Current Week" , "Last Month"
- etc
- etc
You can script these up in a single DAX statement, and I think there are a few floating around from other experienced posters on here .
There are also tricks you can do to guarantee the table you create is actually marked as a DATE table in the underlying Tabular cube.
Phil,
You are right. The only way I get DAX Date Functions to work (prior periods) is when using the Date table.
My twisted thinking is that calculations get confused when there is just something different about a date. The common table places multiple tables on the same date footing.
- answeriver7 years agoHelper IV
Hi all,
As i understand this function (SamePeriodLastYear) getting work that:
In data i have 2017 Full Year and 2018 before today. I want compare the same periods 2017 VS 2018, but function gives me sales for FULL 2017 year.