Forum Discussion
Last year comparison logic with ERP data
A little update, I managed to create a date table as suggested by amitchandak and was able to successfully make use of SAMEPERIODLASTYEAR. It was a trial and error process and several attempts were made at making my own date table and having done that really fortified the concept. Even so, I kept having small issues. Then I came across an open source project called Bravo for Power BI which created a calendar table in my Power BI project much better than I could have possibly done on my own with tons more options relating to localizations and also introduced a lot of handy time intelligence measures as part of the process. I'm a very happy camper.
formerlyknownas , with Time intelligence you should use a date table and no date hierarchy . Have column in you date table what ever you need
LY = CALCULATE(SUM('Sales'[SaleAmt]), SAMEPERIODLASTYEAR('Date'[DocDate]))
refer to my blog and video
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
example Date table
Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)
Thanks amitchandak. I have watched the video and understand the approach, now I just need to apply it with a little trial and error. I'll let you know how it goes.