How to handle SELECTEDVALUE in calculated column for Expired status based on selected date dynamical

[Vidya111]

Hi all,

Is anyone facing a similar issue in Power BI?

I need to show Won, Lost + Expired, and their ratio in a clustered bar + line chart. The status depends on a user-selected Effective Date (usually 1st of each month).

I created an Effective Date table and used SELECTEDVALUE() to calculate if a quote is aged (i.e., Quote Created Date is more than 60 days before the Effective Date).
If aged, I mark it as Expired, else keep the original status (Won, Lost, Open).

Everything works fine so far.

Now the issue:
I need to show these statuses over time based on:

• Delivery Date for Won

• Opportunity Closed Date for Lost

• Quote Created Date + 60 days for Expired

I created a Graph Month calculated column for this. But for Expired, it fails because the Effective Date is selected by the user and can’t be used in a calculated column.

How can I show all these statuses correctly in a bar chart with their proper dates when Effective Date is dynamic?

below measure I used, it works perfectly

Final PL Status Measure =
VAR SelectedEffectiveDate = [Selected Effective Date]
VAR QuoteCreatedDate = MAX('Quotes'[Created DateTime])
VAR PipelineStatus = SELECTEDVALUE('Quotes'[Pipeline Status])
VAR DeviceID = SELECTEDVALUE('Quotes'[Device ID])

-- Get Delivery Date from Invoices table using Device ID
VAR DeliveryDate =
CALCULATE(
MAX('Invoices'[Invoice Date]),
FILTER(
'Invoices',
'Invoices'[Device ID] = DeviceID
)
)

-- Aged if QuoteCreatedDate is more than 60 days before selected effective date
VAR IsAgedQuote = DATEDIFF(QuoteCreatedDate, SelectedEffectiveDate, DAY) > 60

-- Consider expired only if effective date is in the past
VAR IsAgedExpectedOrderDate = SelectedEffectiveDate < TODAY()

VAR Expired = IsAgedQuote && IsAgedExpectedOrderDate

-- If we have a delivery date, treat as Won
VAR FinalPipelineStatus =
IF(
NOT ISBLANK(DeliveryDate),
"Won",
PipelineStatus
)

RETURN
SWITCH(
TRUE(),
FinalPipelineStatus = "Open" && Expired, "Expired",
TRUE(), FinalPipelineStatus
)

But I am facing issue with below calculated column for expired status.I can't create this as measure because I need to use this on bar graph x axis.

Graph Month =
VAR DeliveryDate =
CALCULATE(
MAX('Invoices'[Invoice Date]),
FILTER(
'Invoices',
'Invoices'[Device ID] = 'Quotes'[Device ID]
)
)
RETURN
SWITCH(
TRUE(),

[Final PL Status Measure] = "Won",
EOMONTH(DeliveryDate, -1) + 1,

[Final PL Status Measure] = "Lost",
IF(
ISBLANK('Quotes'[Custom Closed Date]),
EOMONTH('Quotes'[Opportunity Closed Date], -1) + 1,
EOMONTH('Quotes'[Custom Closed Date], -1) + 1
),

[Final PL Status Measure] = "Expired",
DATEADD('Quotes'[Created DateTime], 60, DAY),

BLANK()
)

Thanks in advance!

[v-saisrao-msft]

Hi @Vidya111,

I hope you had a chance to review the solution shared by @Demert @danextian @Akash_Varuna . If it addressed your question, please consider accepting it as the solution — it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[Akash_Varuna]

Hi @Vidya111 Calculated columns can’t respond to slicer values like Effective Date since they’re static. Instead, create a measure for Graph Month that dynamically calculates dates based on user selection. Use a calculated table to generate unique months for the X-axis and relate it to your dataset.

[Vidya111]

Hi, thanks for your response.

I tried a workaround by creating the necessary logic using measures. I built a separate Graph Month table with the 1st of each month and then created individual measures accordingly.

However, the measure for the "Expired" status is still returning blank or no values. Here's the DAX measure I used:
StatusCountMeasure-Expired =
VAR SelectedGraphMonth = SELECTEDVALUE('GraphMonthTable'[Date])
RETURN
SUMX(
'Quotations_View',
VAR CreatedDate = 'Quotations_View'[Created DateTime]
VAR ExpiredDate = CreatedDate + 60
VAR ExpiredMonthStart = EOMONTH(ExpiredDate, -1) + 1
VAR Status1 = [Final PL Status Measure1]
RETURN
IF(
Status1 = "Expired" && ExpiredMonthStart = SelectedGraphMonth,
1,
0
)
)
Could you please help me identify why this measure might not be returning the expected results?

[Demert]

Hi @Vidya111 ,

In this case I would move away from using calculated columns because your output is dynamic and based on report filtering. What you can do is make use of a stacked bar chart for example and create per status a measure and drag them all into the visual which would look like this.

[Vidya111]

Hi, thanks for your response.

I tried a workaround by creating the necessary logic using measures. I built a separate Graph Month table with the 1st of each month and then created individual measures accordingly.

However, the measure for the "Expired" status is still returning blank or no values. Here's the DAX measure I used:
StatusCountMeasure-Expired =
VAR SelectedGraphMonth = SELECTEDVALUE('GraphMonthTable'[Date])
RETURN
SUMX(
'Quotations_View',
VAR CreatedDate = 'Quotations_View'[Created DateTime]
VAR ExpiredDate = CreatedDate + 60
VAR ExpiredMonthStart = EOMONTH(ExpiredDate, -1) + 1
VAR Status1 = [Final PL Status Measure1]
RETURN
IF(
Status1 = "Expired" && ExpiredMonthStart = SelectedGraphMonth,
1,
0
)
)
Could you please help me identify why this measure might not be returning the expected results?

[danextian]

hi @Vidya111 

Calculated columns and tables are not aware of slicer selections so SELECTEDVALUE inside a column will return blank unless you supply an alternate value.  If you want to use the value of a measure, it needs to be materialized using a physical table which usually doesnt have a relationshiop with the other fact and dimension tables.  Attached are sample pbix files related to that.

If you want a more workable solution, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.