Forum Discussion
passing date slicer value in calculated column
Requirement:
Start of Period and End of Period are based on user selection
Eg: Q3 2021 ( start of period: 1/7/2021 & End of Period 30/9/2021)
I have to create calculated columns as below
Undetermined Beginning of Period = IF( TrusteeClaimsList_Received[LeadLagReceivedDate]<> BLANK() && TrusteeClaimsList_Received[LeadLagReceivedDate]<= Start of Period
&& (ISBLANK(TrusteeClaimsList_Received[LeadLagDecisionEndDate]) || TrusteeClaimsList_Received[LeadLagDecisionEndDate]>= Start of Period)
&& (ISBLANK(TrusteeClaimsList_Received[CaseReopenDate]) || TrusteeClaimsList_Received[CaseReopenDate] <= start of period)
,1,0)
And
Age of Pending Claims = IF(TrusteeClaimsList_Received[LeadLagReceivedDate] <> BLANK()
&& ISBLANK(TrusteeClaimsList_Received[LeadLagDecisionEndDate]),
DATEDIFF(TrusteeClaimsList_Received[LeadLagReceivedDate],[End of Period],DAY),BLANK())
When user selects Q3 the start of period and end of period in the formulas should adjust accordingly.
What I did so far:
Filter using date slicer:
Created 2 measures,
- start of period = CALCULATE(MIN('Date Table'[Date]),ALLSELECTED('Date Table'))
- End of period = End of Period = CALCULATE(MAX('Date Table'[Date]),ALLSELECTED('Date Table'))
So, when user selects the Q3 the formulas should automatically take min(1/7/2021) and max(30/9/2021) of the Q3 and apply in formulas
But, my visuals are not filtering accordingly.
Please help me what should I do here?
7 Replies
halfglassdarklyResponsive Resident
Try creating these as measures instead of calculated columns. Calculated columns aren't going to inherit filter context from your slicers via your measures.
I cannot use column names in my measure right?
Age of Pending Claims = IF(TrusteeClaimsList_Received[LeadLagReceivedDate] <> BLANK()
&& ISBLANK(TrusteeClaimsList_Received[LeadLagDecisionEndDate]),
DATEDIFF(TrusteeClaimsList_Received[LeadLagReceivedDate],[End of Period],DAY),BLANK())
Can you please show me?
TrusteeClaimsList_Received[LeadLagReceivedDate] this is a column from table
How can I use this in Measure ?
- halfglassdarkly
Try using selectedvalue(TrusteeClaimsList_Received[LeadLagReceivedDate])
when referring to your column names.
You will still need to use the measure in a visual table or other visual where it can inherit row level context from your other fields.
https://docs.microsoft.com/en-us/dax/best-practices/dax-selectedvalue
- halfglassdarkly
It's not going to work on its own because it needs the context from the columns it's referencing - they need to be added to your visual table as well.