Forum Discussion
calculate two adjacent columns based on two different time spans in a matrix visual
- Anonymous2 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
Sample excel raw data and PBI snapshot.
- Anonymous2 years agoNot applicable
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