Sum and Percentage Calculation of current 3 months

[RilwanFlame]

Hello there - I hope to get help as i am having a little trouble with my DAX calculation. I want to calculate Hedge Ratio and Dollar Mismatch of the current 3 months, 6 months, 12 and 24. 

The result i want is to look like below: 

To calculate Hedge Ration = Sum of the Current 3months of Column G divided by the Sum of the Current 3 months of Column H. 

While to Calculate Dollar Mismatch= Sum of the Current 3months of Column J

Thank you. 

[v-yanjiang-msft · Accepted solution]

Hi @RilwanFlame ,

According to your expected results, it can be calculated that you take the date framed by the red line(2021/5/10-2021/6/10) as rolling 3 months, and the date framed by the green line(2021/3/25-2021/6/10) as rolling 6 months.

Actually that's incorrect, I guess it's an error and create the below sample.

Here's my solution:

1.Create a Rolling Period table, here my sample data only contains the rolling 6 month, so there're only 3 and 6 in my table, you can add 12 and 24 as you needed.

2.Create two measures.

Hedge Ration =
VAR _TotalAsset =
    CALCULATE (
        SUM ( 'Table'[MZ SPX Secure Asset] ),
        DATESINPERIOD (
            'Table'[Hedge Start],
            MAX ( 'Table'[Hedge Start] ),
            - MAX ( 'Rolling Period'[Rolling Period] ),
            MONTH
        )
    ) + 0
VAR _TotalLiab =
    CALCULATE (
        SUM ( 'Table'[MZ SPX Secure Liab] ),
        DATESINPERIOD (
            'Table'[Hedge Start],
            MAX ( 'Table'[Hedge Start] ),
            - MAX ( 'Rolling Period'[Rolling Period] ),
            MONTH
        )
    )
RETURN
    DIVIDE ( _TotalAsset, _TotalLiab )

Dollar Mismatch =
CALCULATE (
    SUM ( 'Table'[MZ SPX Secure Dollar Mismatch] ),
    DATESINPERIOD (
        'Table'[Hedge Start],
        MAX ( 'Table'[Hedge Start] ),
        - MAX ( 'Rolling Period'[Rolling Period] ),
        MONTH
    )
)

3.Put Rolling Period column in Matrix Columns and two measures in Matrix Values, then in the formatting pane>Values>Options, turn on the Switch values to rows option, get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RilwanFlame]

Thank you so much for this. 

[amitchandak]

@RilwanFlame , You need to use calcultion group for that.

refer

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

[RilwanFlame]

Thank you for the response but this does not answer my question.

[amitchandak]

@RilwanFlame , Sorry, seems like I did not get it completely.

For Rolling 3, you can try formulas like using a date table

Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww