Search code examples
matrixpowerbidaxmeasure

Year over Year difference in Power BI matrix with year filter

I have the following dataset:

year category sales
2001 shoes 47,84
2001 pants 106,85
2002 shoes 30,57
2002 pants 103,08
2003 shoes 32,64
2003 pants 85,08

Based on this dataset I create the following matrix in Power BI: enter image description here

I need to calculate the year-over-year difference for two consecutive years including a year filter, so that I get this:

enter image description here

To achieve my desired result, I created the following measure:

Diff =
IF (
    ISFILTERED ( Sheet1[year] ),
    SUM ( Sheet1[sales] ),
    CALCULATE ( SUM ( Sheet1[sales] ), Sheet1[year] = "2003" )
        - CALCULATE ( SUM ( Sheet1[sales] ), Sheet1[year] = "2002" )
)

but instead I got this:

enter image description here

Can you help? thanks

Solution

  • I am adding another answer as I think my first answer can help others. Just follow these below steps.

    Step-1: Create these below 7 measure-

    text_category = "Category"

    text_prev_year = MAX('your_table_name'[year]) - 1

    text_this_year = MAX('your_table_name'[year])

    text_diff = "YoY Diff"

    max_year_sales = 

var max_year = MAX('your_table_name'[year])

return
CALCULATE(
    sum('your_table_name'[sales]),
    FILTER(    
        ALLEXCEPT('your_table_name','your_table_name'[category]),
        'your_table_name'[year] = max_year
    )
)

    prev_year_sales = 

var prev_year = MAX('your_table_name'[year]) - 1

return
CALCULATE(
    sum('your_table_name'[sales]),
    FILTER(    
        ALLEXCEPT('your_table_name','your_table_name'[category]),
        'your_table_name'[year] = prev_year
    )
)

    diff = [max_year_sales] - [prev_year_sales]

    Step-2: Create the year slicer.

    Step-3: Add a table visual and disable Header from the property. Then add these 4 measure to it- "text_category", "text_prev_year", "text_this_year" and "text_diff".

    Step-4: Add another table and do the same - disable Header. Then add one column "category" and 3 measures- "prev_year_sales", "max_year_sales" and "diff" to the table.

    The output will be something like the below-

    enter image description here

    Now just adjust both tables together and column widths in a way so that values in your first table seem like the header of your second table. You can group both tables as well after placing them in the appropriate place. The final output will be something like-

    enter image description here

    Note: This is not a standard solution.