Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |