Forum Discussion
Dynamic 2 Date Columns Difference
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.
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