Dynamic Filtering when using Field Parameters

[JamesAH]

I pretty sure I have hit a dead end after many hours of research and testing but I'd love it if someone can help me solve this one. My goal is to use a Sankey diagram to show changes in data over time. The data is opportunites from a CRM and I want to show the movment in stages. Currently the data is pulled every week and appended to a table with the extraction date. I then pivot this table on the extraction date by stage so I get a column for each extraction date with the stage when it was extracted. I'm then using DAX to dynamically build my Parameter Fileld as I want the user to be able to select two dates (columns) that are then used for the Sankey. As the data is updated every week, it can't be hard coded. This allows me to use the two parameter field to feed into the Sankey Diargram like this: 

 Now the bit I'm stuck on - The issue with this visual is that it shows data when there is no change i.e. records that were in one stage and are still in that stage. I want to be able to filter these out. I've tried to use a measure that compares the two cols when there are slected but the issue is that I can't get it to work as I need. The approach below as an exapmple works but my issue is that the dates are hard coded and every time them data updates with a new extract, this code needs updating too. 

ChangeFlag = 
VAR SourceStatus = 
    SWITCH(
        TRUE(),
        SELECTEDVALUE('ParameterTable'[Parameter]) = "'InputTable'[18/04/2025]", 'InputTable'[18/04/2025],
        SELECTEDVALUE('ParameterTable'[Parameter]) = "'InputTable'[19/04/2025]", 'InputTable'[19/04/2025],
        SELECTEDVALUE('ParameterTable'[Parameter]) = "'InputTable'[20/04/2025]", 'InputTable'[20/04/2025],
        BLANK()
    )
VAR CurrentStatus = 'InputTable'[YourStaticDestinationColumn] 
RETURN
IF(
    SourceStatus <> CurrentStatus,
    1,
    BLANK()
)

Is there any way to make the above code dynamic and biuld based on the number of extraction dates? Or are there other ways to do this? Thanks, 

[JamesAH]

@bhanu_gautam  Thanks, but I dont think this works as I don't have stage as a column any more, the stages are contained in the columns with dates after the data is pivoted. Here is a the data, note that over time the number of columns will expand : 

[bhanu_gautam · Accepted solution]

@JamesAH , Try using

Create a Parameter Table: Ensure you have a parameter table that allows users to select the two dates dynamically.

Dynamic Column Selection: Use the SELECTEDVALUE function to dynamically reference the columns based on the selected dates.

Change Flag Calculation: Calculate the change flag dynamically without hardcoding the dates.

DAX
ChangeFlag =
VAR SelectedDate1 = SELECTEDVALUE('ParameterTable'[Date1])
VAR SelectedDate2 = SELECTEDVALUE('ParameterTable'[Date2])
VAR SourceStatus = LOOKUPVALUE('InputTable'[Stage], 'InputTable'[ExtractionDate], SelectedDate1)
VAR CurrentStatus = LOOKUPVALUE('InputTable'[Stage], 'InputTable'[ExtractionDate], SelectedDate2)
RETURN
IF(
SourceStatus <> CurrentStatus,
1,
BLANK()
)

[JamesAH]

I just solved this! Worked out that rather than using the pivioted table to to check for change I used the original table and the lookups. So thanks @bhanu_gautam as whilst your response wasn't perfect, it gave me the idea.