How to show variance values for latest cal year values and revious cal year values in separate row

[Kusaadigarla01]

Hi All,

I need to show variance of latest calendar year values and Previous Calendar year values in separate row.

above table is created by using matrix table.

Note: Showing the color for variance in latest year also fine. example: if cal-2023 value is < cal-2022 value then we have to show the cal-2023 value in red else the values should appear in Green.

Regards,

Kusa

[Kusaadigarla01]

Hi @FreemanZ , It is really helpful even i can show the variance value in tooltip. But Stakeholder still wants to see the variance values of latest time period and its previous time period as separate row in the report.

[Kusaadigarla01]

@FreemanZ thank you so much for reply.

I have three queries here,

1. How did you apply that background color using the calculated measure? here i am using the matrix table for report creation, i tried with cell elements option but not working.

2. and how can i show the variance color on latest year only. not required for previous years.

3. How can i display variance in tooltip?

[FreemanZ]

hi @Kusaadigarla01 

1.https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

2.try like:

Measure = 
VAR _year = MAX(TableName[Year])
VAR _yearpre = _year-1
VAR _yearmax = MAXX(ALL(TableName[Year]), TableName[Year])
VAR _qty = SUM(TableName[Qty])
VAR _qtypre = 
CALCULATE(
    SUM(TableName[Qty]),
    TableName[Year] =_yearpre
)
RETURN
IF(
    _year=_yearmax,
    IF(
     _qty<_qtypre,
     "red","green"
    )
)

3. tooltip is not supported in Matrix Visual. 

[FreemanZ · Accepted solution]

hi @Kusaadigarla01 

Not sure if i fully get you. Supposing you have a table like this:

you can plot a matrix visual like:

Now try to use a measure like below to conditionally format your value field:

Measure = 
VAR _year = MAX(TableName[Year])
VAR _yearpre = _year-1
VAR _qty = SUM(TableName[Qty])
VAR _qtypre = 
CALCULATE(
    SUM(TableName[Qty]),
    TableName[Year] =_yearpre
)
RETURN
IF(
    _qty<_qtypre,
    "red","green"
)

it worked like:

[Kusaadigarla01]

@FreemanZ,

Thanks for your help, User is okay to show the variance in color code. Still need your help to write variance dax for maximum month and its previous month as well. I am facing issues due to its date format in report (mmm-yyyy). Actual date format in db in dd/mmm/yyyy format, As per requirement changed the date format to mmm-yyyy. unable to write dax query for this date format. check the report format below. 

Regards,

Kusa