Seeking Guidance on Dynamically Changing Date in DAX Calculated Table

[Learner27]

Hello Everyone,

I am currently working on appending tables based on a specific condition. I have created a calculated table using the DAX function below.

Append =

var _selectedDate = "01/05/24" 

VAR IT = CALCULATETABLE(VALUES(Incorrect_table[CustomerNo.]),Incorrect_table[DateFormat] <= _selectedDate)

VAR CT = CALCULATETABLE(VALUES(Correct_table[CustomerNo.]),Correct_table[DateFormat] = _selectedDate)

VAR _NotMatched = EXCEPT(IT,CT)

VAR _TBLA = FILTER(Incorrect_table,Incorrect_table[CustomerNo.] IN  _NotMatched)

VAR  _TBLB= FILTER(Correct_table,Correct_table[DateFormat] = _selectedDate)

VAR BOTHTB = UNION(_TBLA,_TBLB)

Return BOTHTB

However, I've encountered a challenge where the very first variable, _selecteddate, is hardcoded, and I am unsure how to make it dynamic.

My primary requirement is for the selected date in the slicer to dynamically change the value of the variable _selecteddate.
While I understand the suggestion of using a calendar table for dates, my specific need is for the table to be appended based on the selected date.

I kindly request your assistance in this matter. Your guidance and support would be greatly appreciated.

Thank you for your time and consideration.

[Fowmy]

@Learner27 

While creating a table or calculated column in Power BI, capturing slicer selections in real-time isn't feasible. Power BI constructs tables and columns before making them available on the report canvas. Therefore, what you are trying to achieve as per my knowledge is not possible.  Try to achieve the end results using measures after creating a static dates table.

[123abc]

To make the selected date dynamic based on a slicer in Power BI, you can use the SELECTEDVALUE function to get the selected date from the Date slicer. Here's how you can modify your DAX code:

Append =
VAR _selectedDate = SELECTEDVALUE(DateSlicer[Date])

VAR IT = CALCULATETABLE(VALUES(Incorrect_table[CustomerNo.]), Incorrect_table[DateFormat] <= _selectedDate)
VAR CT = CALCULATETABLE(VALUES(Correct_table[CustomerNo.]), Correct_table[DateFormat] = _selectedDate)
VAR _NotMatched = EXCEPT(IT, CT)
VAR _TBLA = FILTER(Incorrect_table, Incorrect_table[CustomerNo.] IN _NotMatched)
VAR _TBLB = FILTER(Correct_table, Correct_table[DateFormat] = _selectedDate)
VAR BOTHTB = UNION(_TBLA, _TBLB)

RETURN BOTHTB

In this modified code, _selectedDate is assigned the value from the Date slicer using the SELECTEDVALUE function. Now, whenever you change the date in the slicer, the _selectedDate variable will dynamically update, and the subsequent calculations will be based on the selected date.

Make sure to replace "DateSlicer" with the actual name of your slicer that contains the date information. Also, ensure that the Date column in the slicer is of the same data type as the Date/Time columns in your tables.

This modification should help you achieve the dynamic behavior you're looking for based on the selected date in the slicer.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Daniel29195]

@Learner27 

create the following measure : 

measure =

var _selectedDate = "01/05/24" 

VAR IT = CALCULATETABLE(VALUES(Incorrect_table[CustomerNo.]),Incorrect_table[DateFormat] <= _selectedDate)

VAR CT = CALCULATETABLE(VALUES(Correct_table[CustomerNo.]),Correct_table[DateFormat] = _selectedDate)

VAR _NotMatched = EXCEPT(IT,CT)

VAR _TBLA = FILTER(Incorrect_table,Incorrect_table[CustomerNo.] IN  _NotMatched)

VAR  _TBLB= FILTER(Correct_table,Correct_table[DateFormat] = _selectedDate)

VAR BOTHTB = UNION(_TBLA,_TBLB)

var calc =  calculate ( distinctcount(table_name[column_name])  , treatas(  BOTHTB , table_name[customer no]))

Return calc 

some modifications of the code are needed as , table names, the aggregation function in the calc  variable 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[Learner27]

Hi @Daniel29195 ,

In my DAX(Calculated Table), I hardcoded the date for testing purposes.
However, I now aim to utilize a dynamic date.

My specific requirement is to select a particular date, and on that chosen date, the records should union as outlined in my DAX.

[Walter_W2022]

you can make it dynamicly to use Today()/Now() function, for example, the selected date is 1 year/1 month back from Today/Now. if based on selected value, you can use SELECTEDVALUE function.

[Learner27]

Hello @Walter_W2022 ,

I'm having difficulty understanding the logic you suggested. Could you please provide an example using my DAX, or if you have a preferred approach, share that?

[Walter_W2022]

Sorry for the confusion, would you please be able to upload you BPIX file?