Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
Thank you so much for this.
@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
Thank you for the response but this does not answer my question.
@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
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |