Count in dim table filtered by start and end date

[RichterSeb]

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

[v-xinruzhu-msft · Accepted 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.

[RichterSeb]

Awesome. Thanks a lot. It worked.