Dynamic 2 Date Columns Difference

[sammy339]

You are provided with a dataset containing three date columns (Column1, Column2, and Column3) for multiple projects. The user wants to dynamically calculate the date difference between any two selected date columns using dropdown selections (parameters) in Power BI. For example, selecting Column3 and Column1 should return the difference in days .

How can you achieve this functionality in Power BI to allow users to choose the two date columns dynamically and display the calculated date difference?

[dharmendars007]

Hello @sammy339 , 

You can try using field parameter to achive the above logic..

1. Add all the date columns (Column1, Column2, Column3) into the parameter >> Name the parameter something like DateSelector

DateSelector = {
("Column1", NAMEOF('Table'[Column1]), 0),
("Column2", NAMEOF('Table'[Column2]), 1),
("Column3", NAMEOF('Table'[Column3]), 2)}

2. Duplicate the DateSelector table and rename it as DateSelector2

3. Create Two Slicers with DateSelector(Dropdown1) and DateSelector2(Dropdown2)

4. You now need a measure to calculate the difference between the two selected dates

Date Difference =
VAR SelectedDate1 = SELECTEDVALUE(DateSelector[DateSelector Value])
VAR SelectedDate2 = SELECTEDVALUE(DateSelector2[DateSelector Value])
RETURN
IF(
ISBLANK(SelectedDate1) || ISBLANK(SelectedDate2),
BLANK(),
DATEDIFF(SELECTEDVALUE('Table'[SelectedDate2]), SELECTEDVALUE('Table'[SelectedDate1]), DAY))

5. Add the project names and this Date Difference measure to a table visual.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[sammy339]

Thank you Dharmendar for replying. I am getting following error when trying to use date difference measure. Can you please upload a sample project.

Error Message :

MdxScript(Model) (4, 21) Calculation error in measure 'Table'[Date Difference]: Column [DateSelector] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

[v-sgandrathi · Accepted solution]

Hi @sammy339,

Thank you for reaching out to the Microsoft Fabric Community forum.

create a mapping table first like below

DateSelector = DATATABLE("DateName", STRING, "ColumnIndex", INTEGER,{{"Column1", 1},{"Column2", 2},{"Column3", 3}})

DAX Measure:

Date Difference = 
VAR SelectedCol1 = SELECTEDVALUE(DateSelector[DateName])
VAR SelectedCol2 = SELECTEDVALUE(DateSelector2[DateName])

VAR Date1 = SWITCH(TRUE(),SelectedCol1 = "Column1", 'Projects'[Column1],
        SelectedCol1 = "Column2", 'Projects'[Column2],
        SelectedCol1 = "Column3", 'Projects'[Column3])

VAR Date2 = SWITCH(TRUE(),SelectedCol2 = "Column1", 'Projects'[Column1],
        SelectedCol2 = "Column2", 'Projects'[Column2],
        SelectedCol2 = "Column3", 'Projects'[Column3])

RETURN
IF(ISBLANK(Date1) || ISBLANK(Date2),BLANK(),DATEDIFF(Date1, Date2, DAY))

I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

If this post clears your doubt, please give us Kudos and consider marking Accepting it as a solution to guide other members in finding it more easily.

Best Regards,
Sahasra.

[v-sgandrathi]

Hi @sammy339,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Regards,
Sahasra.

[v-sgandrathi]

Hi @sammy339,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thanks.

[v-sgandrathi]

Hi @sammy339 ,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Regards,
Sahasra.

[sammy339]

Thanks Dharmendar for replying. I tried the above steps and upon using Date Difference in a matrix visual getting error. Is it possible to upload a sample project. Error Message : MdxScript(Model) (4, 21) Calculation error in measure 'Table'[Date Difference]: Column [DateSelector] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.