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,

Could you confirm if the issue has been resolved? We had requested the sample PBIX file to assist you further. If you have found a solution, we would appreciate it if you could share it here to help others in the community.

As we haven’t received a response or the requested sample data, we will proceed to close this thread. If you need further assistance, please raise a new thread in the Microsoft Fabric Community Forum we’ll be happy to support you.

Thank you for being a valued member of the Microsoft Fabric Community.

[v-saisrao-msft]

Hi @Vidya111,

Following up again as we still haven't received the required sample data or clarification to assist with your issue.

Please provide:

• Sample data in text or table format (no screenshots)
• Expected output based on that data
• Any other clarifying details relevant to the issue

Without this, it’s difficult for the community to offer meaningful support.

Thank you.

[v-saisrao-msft]

Hi @Vidya111 ,

Please provide sample data or PBIX file that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? 

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

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

[v-saisrao-msft]

Hi @Vidya111,

We have not yet received a response from you regarding the sample data or pbix file. Since the sample pbix file or data has not been provided, we will be closing this thread. If you have any further issues, please create a new thread for assistance.

Thank you.

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