Forum Discussion
Helper ILast year comparison logic with ERP data
Hi there,
I'm trying to understand more about making last year to date comparisons in a sales dashboard I built. I am importing the data from our ERP on a daily basis where all the data is brought in from one query meaning that there is no separate dimension table for the dates. The dates are exact to sales transactions and I can build interesting reporting using the Date Hierarchy function to see activity by month, quarter, etc. Now I'd like to reflect on performance from last year, so I tried building a LY (Last Year) comparison with a measure like so:
LY = CALCULATE(SUM('Sales'[SaleAmt]), SAMEPERIODLASTYEAR('Sales'[DocDate]))
It works when doing analysis on a daily level, but as soon as introduce Date Hierarchy on my DocDate field, the LY data vanishes.
This means I cannot do LY comparison on a quarterly or monthly basis, which is needed to have a meaningful analysis.
Any ideas on how I can accomplish LY analysis with Date Hierarchy are greatly appreciated.
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.
3 Replies
- amitchandak
Super User
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-bd52912a5bd4example 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")
)
)
formerlyknownasThanks 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.
- formerlyknownas
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.
