Forum Discussion
How to calculate Date Difference for all rows based on dynamic date
Hello guys,
In Table 1, I have 2 date columns and 1 date differnce column. In 4th column, I wrote a simple query to classify date differnce to create groupings. (see screenshot 1). I am showing the count of each grouping on the Dashboard.
The Table 2 is 'Date Table' (see screenshot 2) and both tables are linked with Date columns.
> On Dashboard I also have a filter from Table 2 which will allow user to select random date from past.
> Is it possible to recalculate date difference between Date 1 (from Table 1) and Date selected by user. After this i want to show updated grouping count on dashboard.
I tried creating some measures but it is not evaluating dynamically. Can you help me with this?
lbendlin , Following solution worked for me:-
1. Measure on Date Table:
UserSelectedDate = CALCULATE(MAX(Dates[Date]), ALLSELECTED(Dates[Date]))2. Measure on Table 1:DynamicDateDiff = SUMX(Table1, DATEDIFF(Table1[Date1],[UserSelectedDate ],DAY))I write one more measure to calculate grouping.
5 Replies
You need two measures. One for the datediff
datedifference =
var sel = selectedvalue(slicerdate)
return datediff([date 1],[sel])
and one for the bucketing/grouping
grouping =
switch (true(), datedifference<11,"0-10",datedifference<21,"10-20",..."longer")
lbendlin, Thanks for your reply.
In following measure, how can we use date 1 column as attribute directly in formula. System does not allow to use column while creating measure. Please correct me if I am understanding it wrong.
datedifference =
var sel = selectedvalue(slicerdate)
return datediff([date 1],[sel])
My requirement was to re-evaluate whole DateDiff and Grouping column for each row after user selects the date from slicer, so that I grouping count get directly updated on dashobard.
use max() or selectedvalue() or similar.
I also omitted the last parameter of datediff - you need to set that to days.
Hi Anonymous ,
Just to supplement Ibendlin’s reply, you could use ABS() function to return positive values.
