passing date slicer value in calculated column

[Anonymous]

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,

1. start of period = CALCULATE(MIN('Date Table'[Date]),ALLSELECTED('Date Table'))
2. 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?

[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

[Anonymous]

I tried and nothing shows,

It should take 30/9/2021 as my [End of Period] and filter accordingly, but no data shows 

[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.

[Anonymous]

Not sure what that means ?

Here,[ End of Period] is a measure

[End of Period] = calculate(max('Date Table'[Date]),AllSelected('Date Table'))

And I am using that measure in another measure

Age = IF(SELECTEDVALUE(TrusteeClaimsList_Case[LeadLagReceivedDate]) <> BLANK()

&& ISBLANK(SELECTEDVALUE(TrusteeClaimsList_Case[LeadLagReceivedDate])),

DATEDIFF(SELECTEDVALUE(TrusteeClaimsList_Case[LeadLagReceivedDate]),[End of Period],DAY),BLANK())

And, in my visual page I filtered the page with my date column and  it should take max of the date selection(i.e, 30/9/2021 and filter the results.

[halfglassdarkly]

Your Age measure still needs to be evaluated in a row level context for each value in TrusteeClaimsList_Case[LeadLagReceivedDate] (and any other fields eg Case ID from your TrusteeClaimsList_Case table that you want to report against).

You need to add a visual table with those fields and add your measure to that so that selectedvalue() has access to a single LeadLagRecievedDate per row to evaluate.

[halfglassdarkly]

Try creating these as measures instead of calculated columns. Calculated columns aren't going to inherit filter context from your slicers via your measures.

[Anonymous]

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 ?