Need a dymanic, bucketed "number of days" based on two date columns

[grggmrtn]

Somehow I have the feeling I should be able to do this, but I keep running around in circles with "almost but not quite" solutions - I hope someone here can help! 🙂

Here is an example of our data:

Person Service		Start date	Stop date	DaysList	Number of days	Intervals
1	104		01-01-2016	24-02-2019	(see code)	1151		G. 1000 - 1499 days
1	108		31-01-2017	31-03-2017	(see code)	60		A. < 100 days
1	105		16-03-2017	26-09-2018	(see code)	925		F. 500 - 999 days
2	85		24-10-2017	24-02-2019	(see code)	489		E. 400 - 499 days
2	105		12-04-2016	07-03-2017	(see code)	330		D. 300 - 399 days
3	108		23-11-2018	24-02-2019	(see code)	94		A. < 100 days

• Person is a unique number for each person that has received a service
• Service is a unique id number for each service
• Number of days is generated from 1 + [Stop date] - [Start date]
• Intervals is a conditional column that buckets the number of days. I'm not sure I need this column, but it's there right now.
• Dayslist is a list of dates starting with [Start date] and creating a row for every date until [Stop date]. I needed this for a slicer - Dayslist in this table is related to my date table

//Dayslist
    #"Added DaysList" = Table.AddColumn(#"Added Intervals", "DaysList", each 
        List.Dates([Start date], 1 + Duration.TotalDays([Stop date] - [Start date]), #duration(1,0,0,0))),
    #"ExpandedDaysList" = Table.ExpandListColumn(#"Added DaysList", "DaysList"),
    #"Changed type DaysList" = Table.TransformColumnTypes(#"ExpandedDaysList",{{"DaysList", type date}})

The date slicer I use in my visuals is from my date table, which as said is related to [Dayslist]. This allows me to find out how many "active" persons there were in a period. It works perfectly, so that isn't the problem.

The problem is, that I need to find out how many users were active for how many days (Intervals) during the period that I set my date slicer to.

Example:

I filter the service to "105" using a slicer, and my date slicer to 01.01.2018 -> 31.12.2018.

• Person 1 may have been "active" for 925 days total in "105", but during the period I've sliced, was only active for 269 days. I now need 269 to be bucketed and put into a bar graph (X = intervals, y = number of persons).
• Person 2 was not "active" in "105" in the sliced period, so should not be in the results
• Person 3 was active for 38 days in the sliced period, and should be bucketed as such and put in the bar graph

And of course, the numbers should change when I change my slicer period.

Putting the intervals and number of days in M maybe wasn't smart or necessary, but when I do it in DAX I end up with measures, which (afaik) aren't cooperating when I need to get the numbers into the bar graph.

I hope I'm making sense, and that someone can help? 🙂

[v-piga-msft]

Hi @grggmrtn ,

I have made a test based on your description.

---

Example:

I filter the service to "105" using a slicer, and my date slicer to 01.01.2018 -> 31.12.2018.

• Person 1 may have been "active" for 925 days total in "105", but during the period I've sliced, was only active for 269 days. I now need 269 to be bucketed and put into a bar graph (X = intervals, y = number of persons).
• Person 2 was not "active" in "105" in the sliced period, so should not be in the results
• Person 3 was active for 38 days in the sliced period, and should be bucketed as such and put in the bar graph

---

Here is my test result. By my research, I'm afraid there is only 269 count of person1 for 105 service during the period 01.01.2018 -> 31.12.2018. So the bar chart should be correct.

In addition, Person 3 has no service for 105 so it won't show in the bar chart when selected option is 105.

If you select service 108, it will show like below.

More details, please have a reference of my test attachement.

Best  Regards,

Cherry

[grggmrtn]

Hi Cherry, thanks so much for trying!

I'm afraid your results don't quite work though.

Your first bar chart isn't correct. You have found that person 1 has the service "105" for 269 days during the period, but that person has been bucketed to interval "F. 500-999 days" - which corresponds to the TOTAL number of days, also the days outside of the period.

And I also need the bucket to reflect the number of persons that fall in the interval, and not the number of days.

As of today I've possibly found a solution that works - but I'm still verifying my data.

1. I removed [Number of days] and [Intervals] from M.

2. I recreated the intervals in a seperate table:

Interval name		Interval MIN	Interval MAX
A. < 100 dage		0				99
B. 100 - 199 dage	100				199
C. 200 - 299 dage	200				299
D. 300 - 399 dage	300				399
E. 400 - 499 dage	400				499
F. 500 - 999 dage	500				999
G. 1000 - 1499 dage	1000			1499
H. 1500 - 1999 dage	1500			1999
I. > 2000 dage		2000			20000000000

3. I created a DAX measure for the number of days and one for the number of persons:

Number of days = DISTINCTCOUNT('Original table'[DaysList])

Number of persons = DISCTINCTCOUNT('Original table'[Person])

4. I created a measure (and this is where it gets tricky for me) to put the number of persons into the intervals:

Interval Measure = 
    CALCULATE (
        [Number of persons];
        FILTER (
            VALUES('ID'[Person]);
            COUNTROWS (
                FILTER (
                    Intervals;
                    [Number of days] >= Intervals[Interval MIN]
                        && [Number of days] < Intervals[Interval MAX]
                )
            ) > 0
        )
    )

So far it all seems to work and the values get automatically updated when I change the slicer values of my date period. But today I'll be verifying so we'll see how it goes 🙂