topic Re: Multiple Date Calculations in Slicer from Row data in DAX Commands and Tips

Multiple Date Calculations in Slicer from Row data

common763 — Mon, 26 Aug 2024 20:22:44 GMT

I have a dataset that has multiple different date calculations on the same row.

Date1,2,3,etc. There are 12 separate date differential calculations between about 6-8 different date columns on the same row.

Is there a way to calculate all of those separate calculations then wrap into a slicer? I tried unpivoting but this will only display the actual date and not the calculation. I have it running properly another way but everything is on separate tabs. I included an example of what I am trying to do as I am probably not detailing it very well.

Re: Multiple Date Calculations in Slicer from Row data

ahadkarimi — Mon, 26 Aug 2024 20:38:07 GMT

Hi common763,

Create Calculated Columns for each date difference (e.g., Date1 - Date2, Date1 - Date3, etc.).
Unpivot the Calculated Columns in Power Query to create a long format table.
Use the unpivoted column names as your slicer/filter.
So you can select different date calculations in a slicer.

Did I answer your question? If so, please mark my post as the solution!✔️
Your Kudos are much appreciated! Proud to be a Responsive Resident!

Re: Multiple Date Calculations in Slicer from Row data

jgeddes — Mon, 26 Aug 2024 20:51:38 GMT

Here is a DAX version of ahadkarimi 's answer. (The power query version is way easier for larger datasets.)

categoryTable = var _t1 = SELECTCOLUMNS( surveyTable, "_survey", [Survey], "_category", "cat1", "_value", DATEDIFF([Date1], [Date2], DAY) ) var _t2 = SELECTCOLUMNS( surveyTable, "_survey", [Survey], "_category2", "cat2", "_value", DATEDIFF([Date1], [Date3], DAY) ) var _t3 = SELECTCOLUMNS( surveyTable, "_survey", [Survey], "_category3", "cat3", "_value", DATEDIFF([Date2], [Date4], DAY) ) Return UNION(_t1, _t2, _t3)

Re: Multiple Date Calculations in Slicer from Row data

common763 — Tue, 27 Aug 2024 12:40:36 GMT

I want to try the first solution because that will clean up some of the calculations. Will get back today with response. Thanks for your help.