Iterate automatically trough columns (conditional formatting on matrix table)

[ferk_23]

Hi,

This is the matrix 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

[v-yiruan-msft · Accepted solution]

Hi @ferk_23 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Unpivot these date fields in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk5MSy2pVNJRcgRiQxDWg5DGSrE6SNJOQGygZw4mLcGkBVhBeH5RtoJHfmlRMdQMmLQJ1CwMRU5wSwyAlkBNigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"KPI Name" = _t, Version = _t, #"Oct-23" = _t, #"Nov-23" = _t, #"Dec-23" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Oct-23", type number}, {"Nov-23", type number}, {"Dec-23", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"KPI Name", "Version"}, "Date", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Date", type date}})
in
    #"Changed Type1"

2. Update the formula of measure [color] as below

color = 
VAR _curyear =
    YEAR ( TODAY () )
VAR _curmonth =
    MONTH ( TODAY () )
VAR _date =
    DATE ( _curyear, _curmonth, 1 ) - 1
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _kpi =
    SELECTEDVALUE ( 'Table'[KPI Name] )
VAR _a =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[KPI Name] = _kpi
                && YEAR ( 'Table'[Date] ) = YEAR ( _date )
                && MONTH ( 'Table'[Date] ) = MONTH ( _date )
                && 'Table'[Version] = "A"
        )
    )
VAR _b =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[KPI Name] = _kpi
                && YEAR ( 'Table'[Date] ) = YEAR ( _date )
                && MONTH ( 'Table'[Date] ) = MONTH ( _date )
                && 'Table'[Version] = "B"
        )
    )
RETURN
    IF (
        YEAR ( _seldate ) = YEAR ( _date )
            && MONTH ( _seldate ) = MONTH ( _date ),
        IF ( _a >= _b, "#1AAB40", "#D64554" )
    )

Best Regards

[truptis]

Hi @ferk_23 , try this: 

create a measure 

Last month =
VAR _CurrentDate = MAX('Table'[Date])
VAR _StartMonthDate = EOMONTH(_CurrentDate,-1)
VAR _StartMonth = DATE(YEAR(_StartMonthDate),MONTH(_StartMonthDate),1)
VAR _EndMonth = EOMONTH(_StartMonth,-1)
VAR _lastmonth=
CALCULATE(Table'[date]), >= _StartMonth
&& 'Table'[date] <= _CurrentDate
)
RETURN

_lastmonth

and then use this _lastmonth in place of Oct23

please hit a thumbs up if this helps you. Thanks.

[ferk_23]

Hi,

Thank you for your help but I don't have a column Table[Date] and it's not possible to use a column in the CALCULATE() function.