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

[harish_2203]

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 row and different columns based on two different date spans and its difference. Date spans are not of fixed duration and they may changed later on. Example image added.

Thanks in advance.

[harish_2203]

Sample excel raw data and PBI snapshot.

[Anonymous · Accepted solution]

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

[harish_2203]

https://drive.google.com/file/d/1YQvXt-zkTZiRW1PiZYzti9N6PIR25xJw/view

Please try with this link.

[harish_2203]

Please download the pbix file from the below link.

test.pbix - Google Drive

[Anonymous]

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

[Anonymous]

Hi @harish_2203 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[harish_2203]

@lbendlin @Ritaf1983 @amitchandak Kindly suggest any solution. Thanks in advance.