Forum Discussion

harish_2203's avatar
harish_2203
New Member
2 years ago
Solved

calculate two adjacent columns based on two different time spans in a matrix visual

Hi all,   I need a support in power BI. I have datewise data for a month but more data can be added further.   Now I need to create a matrix visual where I have differnet calculated measures on r...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi harish_2203,

    I'd like to suggest you do unpivot column on these value fields to convert them to Attribute and Value. 

    Unpivot columns - Power Query | Microsoft Learn

    Then you can use the 'Attribute' on the row, 'date' field on the column to design matrix visual. (you can create a slicer with date field to control which date range of records displayed on matrix)

    After these steps, you can create a measure formula with if statement to check current hierarchy level to redirect different expressions to aggregated value field values(detail level) or calculate the difference based on detail level.

    formula =
    VAR _start =
        MIN ( Table1[Date] )
    VAR _end =
        MAX ( Table1[Date] )
    RETURN
        IF (
            HASONEVALUE ( Table[Date] ),
            SUM ( Table[Value] ),
            CALCULATE (
                CALCULATE ( SUM ( Table1[Value] ), [Date] = _end )
                    - CALCULATE ( SUM ( Table1[Value] ), [Date] = _start ),
                ALLSELECTED ( Table1 ),
                VALUES ( Table1[Attribute] )
            )
        )
    

    Regards,

    Xiaoxin Sheng