How to calculate Date Difference for all rows based on dynamic date

[Anonymous]

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?

[v-eachen-msft]

Hi @Anonymous ,

Just to supplement Ibendlin’s reply, you could use ABS() function to return positive values.

[lbendlin]

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

[Anonymous]

@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.

[lbendlin]

use max() or selectedvalue() or similar.

I also omitted the last parameter of datediff - you need to set that to days.

[Anonymous · Accepted solution]

@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.