cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_keith
Frequent Visitor

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

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

12017-01-012017-01-02
22017-01-019999-99-99
32017-01-029999-99-99
42017-01-022017-01-06
52017-01-022017-01-09
62017-01-039999-99-99
72017-01-049999-99-99
82017-01-059999-99-99
92017-01-059999-99-99
102017-01-052017-01-08
112017-01-069999-99-99
122017-01-109999-99-99

 

Thanks!

Keith

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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

 

q6.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

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

 

q6.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.