Forum Discussion

common763's avatar
common763
Icon for Helper III rankHelper III
2 years ago
Solved

Multiple Date Calculations in Slicer from Row data

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. 

Need help for Dax
Slicer
  • jgeddes's avatar
    jgeddes
    2 years ago

    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)



3 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    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)



  • ahadkarimi's avatar
    ahadkarimi
    Icon for Solution Specialist rankSolution Specialist
    2 years ago

    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!

  • common763's avatar
    common763
    Icon for Helper III rankHelper III
    2 years ago

    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.