topic Re: How to show variance values for latest cal year values and revious cal year values in separate row in DAX Commands and Tips

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

Kusaadigarla01 — Thu, 06 Apr 2023 11:13:28 GMT

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

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

FreemanZ — Thu, 06 Apr 2023 14:00:14 GMT

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:

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

Kusaadigarla01 — Fri, 07 Apr 2023 06:31:44 GMT

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?

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

FreemanZ — Fri, 07 Apr 2023 07:28:13 GMT

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.

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

Kusaadigarla01 — Wed, 12 Apr 2023 05:01:30 GMT

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.

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

Kusaadigarla01 — Thu, 20 Apr 2023 15:39:07 GMT

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