Sales and Prior Year Sales In A Matrix

[danielpaduck]

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!

[FreemanZ]

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.

[danielpaduck]

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

[amitchandak · Accepted solution]

@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-bd52912a5b...
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.

[danielpaduck]

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