Highlight the decrease data – Microsoft Fabric Community

Scenario:

Sometimes we want to compare the data from certain month with the data from same month but in previous year.  

For example you may want to compare the sales data for each month in 2019 vs. 2020. You may not easily find out that which months in 2020 has more sales compared to 2019 and which month has relatively negative result compared to 2019.  

Therefore we have a way to highlight the decrease data.

Sample data:

This is a Column chart generated with the month as axis and sales as value. It is difficult to find out which month that sales were decreased.

Operations:

We could use a DAX Measure to make a color mark for these months.

Measure =

var month_ = SELECTEDVALUE('Table'[month])

var sales_ = CALCULATE(SUM('Table'[sales]),'Table'[month]=EDATE(month_,-1))

return

IF(SELECTEDVALUE('Table'[sales])>=sales_,"#395871","#9a3935")

Then set the data color as below.

And the result would be shown as below. We could tell at a glance that the sales value of these months were decrease.

Extend:

In practical applications, not only compare the difference between this month and the previous month, we can also compare the difference from the same month of the previous year.

Through the following steps, we can select the year from the slicer then compare with the value of the previous year.

First, we use a formula to set the default value of the slicer, if the slicer was not selected, the visual only show the values from year

2018:

flag =

IF (

    ISFILTERED ( 'Table'[month].[Year] ),

    1,

    IF ( YEAR ( SELECTEDVALUE ( 'Table'[month] ) ) = 2018, 1, 0 )

)

Then make a little modification to the above statement:

Measure2 =

var month_ = SELECTEDVALUE('Table'[month])

var sales_ = CALCULATE(SUM('Table'[sales]),FILTER(ALL('Table'),'Table'[month]=EDATE(month_,-12)))

return

IF(SELECTEDVALUE('Table'[sales])>=sales_,"#395871","#9a3935")

And here’s the final result: