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
- Anonymous2 years agoNot applicable
Hi harish_2203,
It seems like I can't view the share link, can you please create some dummy data and paste them here with table format? Then we can simply copy them to power bi side and test and coding formula on it.
Regards,
Xiaoxin Sheng