Forum Discussion
Dynamic Status Change for events based on selected date range
Hi all,
I am creating a report which requires, amongst other things, a view of training sessions that have been completed, are planned, or have been cancelled (I can potentially get rid of this last category but leaving it in for now). The issue is that the info about these sessions is recorded in an Excel register which is updated dailiy - however, our reporting needs to be point in time and aligned with various other data sources. I use a slicer to set the date range of the report, which is fine works fine for the other sources of data, but of course planned courses (and courses that have been completed, their status updated, but occurred after the specified point in time of the report) won't appear - and we want to be able to see what courses are planned.
Is there a way to dynamically change the status of a training session from planned to completed based on the max selected date selected in the slicer? I would also need to create a separate date field that takes the date of the training session when status is "Completed" and takes the max date selected by the filter when the training session is "Planned", so that these future sessions can be included in the dashboard, and this would presumably need to be the date field that relates back to my date table.
Relevant data looks like this:
| Status | Date |
Completed | 29/03/26 |
| Completed | 30/03/26 |
| Completed | 01/04/26 |
| Cancelled | 02/04/26 |
| Completed | 03/04/26 |
| Completed | 04/04/26 |
Completed | 05/04/26 |
| Planned | 18/04/26 |
| Planned | 19/04/26 |
| Planned | 20/04/26 |
| Planned | 21/04/26 |
| Planned | 22/04/26 |
What I'd like is for when I set the date range to end at, for example, 03/04/26, any "Completed" sessions after this date become "Planned" (this can be a new column/measure obviously), and a new date measure for all entries after this date becomes 03/04/2026. In table form, this would look like this:
| Status | Date | Dynamic Status | Dynamic Date |
Completed | 29/03/26 | Completed | 23/03/26 |
| Completed | 30/03/26 | Completed | 30/03/26 |
| Completed | 01/04/26 | Completed | 01/04/26 |
| Cancelled | 02/04/26 | Cancelled | 02/04/26 |
| Completed | 03/04/26 | Completed | 03/04/26 |
| Completed | 04/04/26 | Planned | 03/04/26 |
Completed | 05/04/26 | Planned | 03/04/26 |
| Planned | 18/04/26 | Planned | 03/04/26 |
| Planned | 19/04/26 | Planned | 03/04/26 |
| Planned | 20/04/26 | Planned | 03/04/26 |
| Planned | 21/04/26 | Planned | 03/04/26 |
| Planned | 22/04/26 | Planned | 03/04/26 |
Would this be possible? Or is there a better way of achieving what I'm trying to achieve?
Hi jsjulian90 ,
Thank you for the additional details. From my end, I reproduced the scenario using sample training session data and a Date slicer, and I was able to achieve the required dynamic behavior by using a disconnected Status table together with a measure-based approach instead of a calculated column. I created a small Status table containing the values Completed, Planned, and Cancelled, used it in the Matrix rows without creating relationships, and then used a measure to dynamically evaluate each session against the selected max date from the slicer.
During testing, the measure correctly reclassified future Completed sessions as Planned while keeping Cancelled sessions unchanged, and the Matrix visual updated dynamically based on the slicer selection. I have attached the sample PBIX file used for reproduction and testing from my end for your reference.
Hope this helps.
Please feel free to reach out if you need further assistance.
Regards,
Community Support Team.
12 Replies
Hi jsjulian90 ,
You can achieve it by creating below DAX measures
Dynamic Date = VAR MaxDate = [Selected Max Date] VAR SessionDate = SELECTEDVALUE(Training[Session Date]) VAR StatusValue = SELECTEDVALUE(Training[Status]) RETURN SWITCH( TRUE(), StatusValue = "Cancelled", SessionDate, SessionDate > MaxDate, MaxDate, StatusValue = "Completed" && SessionDate <= MaxDate, SessionDate, MaxDate )Dynamic Status = VAR MaxDate = [Selected Max Date] VAR SessionDate = SELECTEDVALUE(Training[Session Date]) VAR StatusValue = SELECTEDVALUE(Training[Status]) RETURN SWITCH( TRUE(), StatusValue = "Cancelled", "Cancelled", SessionDate > MaxDate, "Planned", StatusValue = "Completed" && SessionDate <= MaxDate, "Completed", "Planned" )I've also created a sample .pbix file for you, Please see attached.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi - I realised I accidentally replied to the post rather than your reply before. Is it possible to do this in such a way that the dynamic status can be used as a dimension? Creating a calculated table doesn't work as it doesn't seem to update based on the slicer.
Hi jsjulian90,
Sorry, I overlooked your message, Kindly Tag once you reply back. Attached is the updated .pbix file for your reference, I created a separate disconnected table and created a measure "Show Row" to reference it and then i just applied a visual level filter on the table view.
Hi jsjulian90,
I would also take a moment to thank grazitti_sapna , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Regards,
Community Support Team.Thank you for this! It's almost right. Unfortunately, what I want is to present a table that looks like this:
Status Location 1 Location 2 Location 3 Total Completed Planned Cancelled However, when I try to build the table, putting Dynamic Status in the Rows, I get the error "This field can't be used here because a non-measure field is required". Is there any way to convert this Dynamic Status, or recreate it, such that it acts as a dimension, or otherwise be used in the rows/columns of tables?
Hi jsjulian90,
Instead of creating a measure you can create a calculated column in order to use it as a dimension.
Thanks for the reply! Unfortunately when I try to do that by going to the model view -> right click query -> add column and paste the same code in, the statuses all become stuck as "Planned" and don't change with the date selected. Is there a different way I should be approaching setting this up as a column?
