Forum Discussion
Solved
Multiple Years Filter
Please reference the DAX formula below. This formula works if the date slicer is within the same year, so 1/1/2024-12/31/2024 works. If the slicer is set to 2/1/2024-1/31/2025 it does not work becaus...
Hi kellanbochenek ,
We really appreciate your efforts and for letting us know the update on the issue.Please continue using fabric community forum for your further assistance.Please raise support ticket to resolve the issue,to raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.
Thank you for being a part of Microsoft Fabric Community Forum.
I tried this formula too. It is basically my old formula but adding in the MIN and MAX dates as you suggested but same result.
Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR EndNetExposure_WithinRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
)
VAR EndNetExposure_OutsideRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
('FactPolicyEndorsement'[ReceivedDate] < MinDate || 'FactPolicyEndorsement'[ReceivedDate] > MaxDate)
)
)
RETURN
IF(
CurrentRecordType = "Pol",
PolNetExposure + EndNetExposure_WithinRange,
IF(
CurrentRecordType = "End" &&
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
) > 0,
0,
'FactPolicyEndorsement'[TotalExposure]
)
)