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 their own interval in stead of a hardcoded interval :

 

Example : 

Study Duration Months = DATEDIFF(REGISTRATIONS[REGISTER_DATE]; TODAY();MONTH)  (this is a column field!)
 
Here I want to replace MONTH by a selectedvalue value that comes from my own table where I have my list of choices  (MONTH, DAY, WEEK , QUARTER)
 
It seems that powerbi is not accepting this syntax?!  :smileymad:  Anyone a solution?
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
      )

     

3 Replies

  • 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
      )