topic Sales and Prior Year Sales In A Matrix in DAX Commands and Tips

Sales and Prior Year Sales In A Matrix

danielpaduck — Fri, 28 Apr 2023 19:53:46 GMT

Hi,

Maybe this has been answered before but I want to show on a Matrix the following columns: Year, Revenue, Last Year Revenue.

My data set has a reference date and a revenue column. I created a measure called 'Total Revenue (msr)'. Then I created a measure like the following:

Total Revenue LY (msr) = Calculate([Total Revenue (msr)], SAMEPERIODLASTYEAR('Revenue By Year'[ReferenceDate]))

The only way I can show last year revenue on the matrix is to actually show the reference date. For this visual, I only wanted to show the Year portion of the transaction date. Thus, rolled up to year.

Thanks!

Re: Sales and Prior Year Sales In A Matrix

amitchandak — Fri, 28 Apr 2023 20:40:04 GMT

danielpaduck , first of all, create a date table, having year, month , qtr etc. Join it with your date and create measure like. User period from date table in visual, slicer and measures

Total Revenue LY (msr) = Calculate([Total Revenue (msr)], SAMEPERIODLASTYEAR('Date'[Date]))

Date table code example

Date= 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])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "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")
)
)

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
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Sales and Prior Year Sales In A Matrix

FreemanZ — Fri, 28 Apr 2023 23:24:24 GMT

hi danielpaduck

try like:Total Revenue LY (msr) = Calculate([Total Revenue (msr)], EDTAE('Revenue By Year'[ReferenceDate], -12))

p.s. in the long run, you shall be working with Time Intelligence functions for such cases.

Re: Sales and Prior Year Sales In A Matrix

danielpaduck — Sat, 29 Apr 2023 13:27:54 GMT

Hi, My Power BI is not recognizing the EDTAE function that you referenced. Is that in a newer version of Power BI? Thanks!

Re: Sales and Prior Year Sales In A Matrix

danielpaduck — Sat, 29 Apr 2023 13:28:37 GMT

Hi - yes, this worked perfectly! Thanks! Do you have any books you can recommend for DAX? Thanks!