Iterate automatically trough columns (conditional formatting on matrix table)

[ferk_23]

Hi,

This is the table created:

I'm using the following measure to do conditional formatting: 
color =
var _a=CALCULATE(SUM('Table'[Oct'23]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="A"))
var _b=CALCULATE(SUM('Table'[Oct'23]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="B"))
return IF(_a>= _b,"#1AAB40", "#D64554")

The problem is that instead of Oct'23 I want an automatic way to always have the last month. In other words, now we are in November, we will use the values from the Oct'23 column. In December, the values in the Nov'23 column and so on.

Any tip is welcome. 

Best regards,
Fernando

[ferk_23]

Any more suggestions? I haven't managed it yet because I have the months as columns

[OguzMavice]

Hi @ferk_23 , 

The following link can solve your problem; instead of using hard coded "month name" in your sum formula; you should use previous month total sum logic. 

https://community.fabric.microsoft.com/t5/Desktop/using-dax-formula-to-calculate-the-previous-month-...

I hope it helps you

[FreemanZ]

hi @ferk_23 ,

The point is to unpivot your data like:

KPI Name, Version, Month, Value

then everything becomes natural like:

color =
var _a=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="A"))
var _b=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="B"))
return IF(_a>= _b,"#1AAB40", "#D64554")

About Unpivot:

https://learn.microsoft.com/en-us/power-query/unpivot-column

[ferk_23]

Thank for your help. 

The columns are now unpivot columns, you know how can I filter to the last month?