Charting The Number of Assets Over Time - Effective From / Effective To

[_keith]

Hello, thank you in advance!

I'm working with a table of assets that I'm trying to represent in a line chart. Over time, show the total number of assets

I'm leveraging a master 'Calendar' table that will serve as the 'X-Axis' but struggling to measure the sum of assets based on the time period that the assets as 'valid'.

Below is and example of what the data looks like.

   Eff_From_Date = Date that the asset was added to system and "valid" (count begining this date)

   Eff_To_Date = Date that the asset was removed from the system and "no longer valid" (do not count after this date)

Asset_ID Eff_From_date Eff_To_Date

1	2017-01-01	2017-01-02
2	2017-01-01	9999-99-99
3	2017-01-02	9999-99-99
4	2017-01-02	2017-01-06
5	2017-01-02	2017-01-09
6	2017-01-03	9999-99-99
7	2017-01-04	9999-99-99
8	2017-01-05	9999-99-99
9	2017-01-05	9999-99-99
10	2017-01-05	2017-01-08
11	2017-01-06	9999-99-99
12	2017-01-10	9999-99-99

Thanks!

Keith

[v-qiuyu-msft · Accepted solution]

Hi @_keith,

As the value "9999-99-99" is not a valid date value, we need to replace it to a valid date, eg: "9999-12-31", so column [Eff_To_Date] can be set as Date format. Then you can create a measure like this. For detail information, you can download attached .pbix file.

Count = CALCULATE(COUNTROWS('Table1'),FILTER(ALL(Table1),'Table1'[Eff_From_date]<=MAX('Calendar'[Date]) && 'Table1'[Eff_To_Date]>=MAX('Calendar'[Date])))

Best Regards,
Qiuyun Yu

[_keith]

Thanks Qiuyun!

In case anyone else was looking to read more about calculating 'Events in progress', the following article by SQL Jason was really helpful: Link