Forum Discussion

StoryDashboard's avatar
StoryDashboard
Frequent Visitor
7 years ago
Solved

Datediff range category defined by selectedvalue

Hi,   We all know the Datadiff function where we show the time difference between two dates. Of course we can calculate the difference in days, months etc.... But if I want the user to choose thei...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    StoryDashboard  As I've mentioned above, I've implemented the same for DateAdd and thought you will follow the same logic for DateDiff.... Anyway, here is the logic for Datediff.... Same Table Visual will show the dates difference in different values based on the type selection. Create a New Measure as below

     

    Test278_1 = 
VAR _Selection = SELECTEDVALUE(Test278Type[Type])
VAR _DateVal = MAX(Test278DateDiffDynamic[Date])
VAR _Day = DATEDIFF(_DateVal,NOW(),DAY)
VAR _Week = DATEDIFF(_DateVal,NOW(),WEEK)
VAR _Month = DATEDIFF(_DateVal,NOW(),MONTH)
VAR _Year = DATEDIFF(_DateVal,NOW(),YEAR)
VAR _Quarter = DATEDIFF(_DateVal,NOW(),QUARTER)

RETURN 
SWITCH(_Selection,
        "DAY",_Day,
        "WEEK",_Week,
        "MONTH",_Month,
        "QUARTER",_Quarter,
        "YEAR",_Year
      )

     

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

StoryDashboard  Please create a new measure as below (Assuming that you have a lookup table which you will be using as filter to select Day,Month,Quarter,Year) In this case, the table name is Type. I've done DateAdd, you can do the same with DateDiff

 

Test278 = 
VAR _Selection = SELECTEDVALUE(Test278Type[Type])
VAR _DateVal = MAX(Test278DateDiffDynamic[Date])

RETURN 
SWITCH(_Selection,
        "DAY",_DateVal+1,
        "MONTH",EDATE(_DateVal,1),
        "QUARTER",EDATE(_DateVal,3),
        "YEAR",EDATE(_DateVal,12)
      )

StoryDashboard's avatar
StoryDashboard
Frequent Visitor
7 years ago

Hi PattemManohar ,

 

This is not where my question was about. The goal is to choose the interval from a lookup table with such values (DAY, MONTH, WEEK,  QUARTER). In that way i could use the same visual where the user can switch if the number of time between two dates is aligned with his choice...

 

So for instance we have two dates 01/01/2019 and today (e.g. 26/03/2019).

If we choose days the duration time is expressed in days and this record got he value 84 because the number of days between today and 01-01-2019 is 84 days.

Now another user with another dataneed chooses for weeks....The user chooses "WEEK" from the Slicer and then he got the answer 4+4+3 = approximately  11 weeks (I do this by heart). All data is now split by duration in weeks! He can use the same visual.:smileyhappy:

 

>> It seems that the last argument of the DATEDIFF DAX function cannot be replaced by a self chosen field!

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    StoryDashboard  As I've mentioned above, I've implemented the same for DateAdd and thought you will follow the same logic for DateDiff.... Anyway, here is the logic for Datediff.... Same Table Visual will show the dates difference in different values based on the type selection. Create a New Measure as below

     

    Test278_1 = 
VAR _Selection = SELECTEDVALUE(Test278Type[Type])
VAR _DateVal = MAX(Test278DateDiffDynamic[Date])
VAR _Day = DATEDIFF(_DateVal,NOW(),DAY)
VAR _Week = DATEDIFF(_DateVal,NOW(),WEEK)
VAR _Month = DATEDIFF(_DateVal,NOW(),MONTH)
VAR _Year = DATEDIFF(_DateVal,NOW(),YEAR)
VAR _Quarter = DATEDIFF(_DateVal,NOW(),QUARTER)

RETURN 
SWITCH(_Selection,
        "DAY",_Day,
        "WEEK",_Week,
        "MONTH",_Month,
        "QUARTER",_Quarter,
        "YEAR",_Year
      )