Forum Discussion
DATEADD using a measure for the interval
Microsoft Employee
Anonymous wrote:
When a user filters a date with a slicer I want to take number of months between them and use that as the interval for a DATEADD.
I built a measure to calculate the months using, which works using a visual;Filtered Months = DATEDIFF(MIN('Calendar'[Date]),MAX('Calendar'[Date]),MONTH)And my column as follows;
Date Shift = DATEADD('Calendar'[Date],[Filtered Date Months],MONTH)When I come to use the Dateshift (which is in the Calendar table) column the results do not change.
Thank you in advance!
Anonymous
It is not possible to create a calculate column/table can change dynamically with user selections on the report. Please post more details of your scenario, maybe creating some tricky measures could help to achieve your goal.
Eric_Zhang Thank you for getting back to me, I thought this might be the case.
What I am trying to do is build a schedule of sessions per month for a hospital on a working day profile. I have built all the measue to calculate the in coming patients and determine how many sessions that works out for a consultant per month on a working day profile.
The in coming patients have a date against them which is joined to a calendar table. When the user uses the slicer and filters the dates I need to work out how many months that is and then push the consultant sessions measure forward that many months.
If a user enters 12 months on the date filter it needs to take the sessions and push the date 12 months ahead.
How it needs to work, example below is based on 2 months;
200 Patients in September 2017, 300 in October 2017,
(((Total 500 * 10min Duration)/ 60 for hours)/ 3.5 for sessions) = 24 sesssions (rounded)
43 working days in Sept 17 & Oct 17; 24 sessions / 43 Wd = 0.56 sessions per working day.
Now 2 months was enter in the slicer, now I need to add the 2 months on to the calendar with the date add and the below is the outcome.
November 17; 22 Working days * 0.56 sessions = 12.32 sessions
December 17; 19 Working days * 0.56 sessions = 10.64 sessions
I have looked into working day calculations for future but I could not find much. Any help on this would be fantastic!