Search code examples
powerbidaxanalysis

Last Year vs Current Selected Year

I have been creating a dashboard in which I am trying to showing current Selected year vs last year analysis. Please see the below image :

enter image description here

As you see in the above image, 2020 year selected from the slicer and 2020 sales is 4.30M. Expectation : I want to show the last year difference with Arrow sign means if the current year sales is greater than last year than "Green upper arrow" and if the last year sales greater than current year then "Red down arrow".

Thing I Tried : I have created a DAX, but it not showing any value to me : Previous Year Sales = CALCULATE(sum(Orders[Sales]),PREVIOUSYEAR(Orders[Order Date]))

Option 2 : I have also tried this one (it show me value but desired results):

same period last year = CALCULATE(Sum(Orders[Sales]),SAMEPERIODLASTYEAR(Orders[Order Date]))

Expected Output ( Current Year vs last year percentage with Arrow sign) :

enter image description here

Sample data link : http://www.cse.ohio-state.edu/~hwshen/Melbourne/Data/Superstore.xlsx How can I achieve the above ?

Thanks

Solution

  • The percentage should be straight foward. Keep in mind you need a DATE TABLE or use the automatic datetime created by power bi, for that you need to reference it with ".[Date]"

    same period last year = CALCULATE(Sum(Orders[Sales]),SAMEPERIODLASTYEAR(Orders[Order Date].[Date]))

Sales YoY % = 
    VAR _ly = [same period last year]
RETURN
    DIVIDE(_ly-Sum(Orders[Sales]),_ly)

    Format you [Sales YoY %] measure as a percentage.

    The visual part you can do it multiple ways, cards, HTML, KPIs Visuals or Table/Matrix conditional formating.