Forum Discussion

ferk_23's avatar
ferk_23
Frequent Visitor
2 years ago
Solved

Iterate automatically trough columns (conditional formatting on matrix table)

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
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • truptis's avatar
    truptis
    Community Champion
    2 years ago

    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's avatar
      ferk_23
      Frequent Visitor
      2 years ago

      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. 


  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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