Forum Discussion
Date Slicer Filtering Issue
Thank you for your help, but the formula does not work. I will paste below the formula I think is close. The issue with this formula is it makes all End 0 if there is a matching Pol from 2019-2025 (my entire dataset). The only End's that are not turned to 0 are those that have a matching Pol from 2018 or earlier, which isn't in my dataset. Even if I set the date slicer to 2024-2025, all End's are still turned to 0 even if the matching Pol is in 2023.
Effective Net Exposure =VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]VAR PolNetExposure = 'FactPolicyEndorsement'[NetExposure]VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))VAR EndNetExposure_WithinRange =CALCULATE(SUM('FactPolicyEndorsement'[NetExposure]),FILTER('FactPolicyEndorsement','FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&'FactPolicyEndorsement'[RecordType] = "End" &&'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&'FactPolicyEndorsement'[ReceivedDate] <= MaxDate))VAR EndNetExposure_OutsideRange =CALCULATE(SUM('FactPolicyEndorsement'[NetExposure]),FILTER('FactPolicyEndorsement','FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&'FactPolicyEndorsement'[RecordType] = "End" &&('FactPolicyEndorsement'[ReceivedDate] < MinDate || 'FactPolicyEndorsement'[ReceivedDate] > MaxDate)))RETURNIF(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'[NetExposure]))
Thank you for your help, but the formula does not work. I will paste below the formula I think is close. The issue with this formula is it makes all End 0 if there is a matching Pol from 2019-2025 (my entire dataset). The only End's that are not turned to 0 are those that have a matching Pol from 2018 or earlier, which isn't in my dataset. Even if I set the date slicer to 2024-2025, all End's are still turned to 0 even if the matching Pol is in 2023.
Hi kellanbochenek,
Thank you for sharing the formula and the detailed explanation of the issue. Based on your description, it seems that the behavior you're experiencing is not as expected, and it may require further investigation to identify the root cause. To ensure timely resolution and expert assistance, I recommend raising a support ticket to ensure we get the necessary assistance and resolution.
Looking forward to your confirmation.
Thank you.
HI kellanbochenek,
Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.
Hi kellanbochenek,
We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?
If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
Thank you for your understanding and participation.