Month slicer for a table with creation and deletion dates

[Tijz]

Hi All,

I have a table which holds device information per customer. Each device also has a creation (acquisition) date, and possibly a deleted (phased out) date. All other data on the report is shown per Month using a slicer.

My problem is  that at the moment only devices with a creation date in the slicer selected month are shown. So when the slicer is set to August 2019, all devices created in august 2019 are shown, but all other devices created prior to that date are left out.

But I want the report to show all devices with

created date <= selected slicer month

AND

deleted date > selected slicer month or deleted = NULL

How can I achieve this?

[TomMartens · Accepted solution]

Hey,

there is name for this kind of problem, the name is events-in-progress.

This blog https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

besides providing a solution for a special it also has links to other resources.

Hopefully this will provide you with some ideas to tackle this challenge.

Regards,

Tom

[TomMartens · Accepted solution]

Hey,

there is name for this kind of problem, the name is events-in-progress.

This blog https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

besides providing a solution for a special it also has links to other resources.

Hopefully this will provide you with some ideas to tackle this challenge.

Regards,

Tom

[Tijz]

Hi Tom,

Thanks again for interpreting my problem and pointing me to those blogs/whitepapers.

I think I implemented their solution, but I'm a little afraid of what is means.

It seems that I have to use DAX to generate a table with potentially millions of records. It will generate a record for each day between an acquisition date and a deletion date of a device. Most devices have a company lifespan of 3 to 5 years. This means potentially generating about 1000 records (3 years) for about 4000 devices, which will result in about 4.000.000 records. I'm wonderering if that won't be a problem for the report.

For my report I would be content with a generated table with one date record for every month between the acquisition and deletion date.

I'm trying to figure out how to do that right now... But I'm fairly new to DAX so it can take me a while.

[Tijz · Accepted solution]

Solved it! Thanks to @TomMartens  for poiting me to the Event-in-Progress scenario.

For my particular scenario I had to tweak it to only compare month instead of every day.

I ended up creating a second Date table (in order to prevent a circular reference) with a extra column presenting the first day of every month. I then worked that into the Generated table as described in the blog @TomMartens referenced.

Test = 
 GENERATE(
     VALUES('Dates2'[FirstDayOfMonth]);
       FILTER(
         CALCULATETABLE('ManagedDevices'; ALL('Dates'));
           CONTAINS(
            DATESBETWEEN(
             Dates[Date];
             ManagedDevices[CreatedDate];
             ManagedDevices[DeletedDateDummy]
           );[Date];'Dates2'[FirstDayOfMonth]
         )
      )
   )

[TomMartens]

Hey @Tijz ,

thanks for sharing your solution.

Regards,

Tom

[Tijz]

Ah cool! I knew this must be a known problem with a solutution, but had no idea how to search for it! Thanks, the blog / whitepapers seem promising. Give me some time to see if I can make this work before I accept it officially as a solution to my question.