Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I need help with the following issue.
Here is an example of the table that I have as input (round about 650 rows):
Machine | WarrantyStart | WarrantyEnd |
A | 01.04.2018 | 30.03.2019 |
B | 03.05.2020 | 02.05.2022 |
C | 02.09.2021 | 01.09.2022 |
D | 19.01.2022 | 18.01.2023 |
E | 20.09.2022 | 19.09.2024 |
Now I need to know, during which time period we had, have and will have machines covered by warranty.
I'm not sure whether this works with a dim date table but I thought it could look like this:
Date | Count of machines in warranty | |
01.04.2018 | 1 | which is machine A |
... | ||
01.01.2022 | 2 | which are machines B and C |
02.01.2022 | 2 | which are machines B and C |
03.01.2022 | 2 | which are machines B and C |
... | ||
01.01.2023 | 2 | which are machines D and E |
... | ||
20.01.2023 | 1 | which is machine E |
... | ||
01.01.2025 | 0 |
First column is just an dim date table starting 01.01.2018 and ends 01.01.2030 and the 2nd column is the column that i need as result.
At the end I like to look 12 monthes back from today and make a forecast for the coming 12 monthes.
I would appreciate any help that I can get.
Many thanks in advance.
Sebastian
Solved! Go to Solution.
Hi @RichterSeb
You can refer to the following example.
The sample table is a date table
Create two columns in the date table
Count = var _filter=COUNTROWS(FILTER('Table',[Date]>=[WarrantyStart]&&[Date]<=[WarrantyEnd]))
return IF(_filter=BLANK(),0,_filter)
machines in warranty = var _fliter=FILTER('Table',[Date]>=[WarrantyStart]&&[Date]<=[WarrantyEnd])
return CONCATENATEX(_fliter,[Machine ],",")
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @RichterSeb
You can refer to the following example.
The sample table is a date table
Create two columns in the date table
Count = var _filter=COUNTROWS(FILTER('Table',[Date]>=[WarrantyStart]&&[Date]<=[WarrantyEnd]))
return IF(_filter=BLANK(),0,_filter)
machines in warranty = var _fliter=FILTER('Table',[Date]>=[WarrantyStart]&&[Date]<=[WarrantyEnd])
return CONCATENATEX(_fliter,[Machine ],",")
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Awesome. Thanks a lot. It worked.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
95 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |