The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 :
Solved! Go to Solution.
@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 )
Proud to be a PBI Community Champion
@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) )
Proud to be a PBI Community Champion
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.
>> It seems that the last argument of the DATEDIFF DAX function cannot be replaced by a self chosen field!
@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 )
Proud to be a PBI Community Champion
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |