Calculate IDs regarding multiple date intervals condition

[KossFrance]

Hi,

I'd like to create a DAX measure to display per month and year number of unique Product ID as soon as the related month and year are included in mulitple intervals.

i.e Jan 2025 should display number of Product ID if Jan 2015 is within interval of element 1 and Element 2 - interval is define by start date to end date. Any idea? Thanks for your support dear community

model

[v-hashadapu · Accepted solution]

Hi , Thank you for reaching out to the Microsoft Community Forum.

Based on your scenario, we created two DAX measures to count unique products that are active in both Element1 and Element2 during a given period. One gives the total per month and the other shows the count per day, based on overlapping date intervals. This accurately captures products active in both intervals, detecting overlaps across time ranges. Everything is visualized cleanly using the Calendar table without needing direct relationships.

Please check the attached .pbix file for your reference.

Thank you.

[v-hashadapu · Accepted solution]

Hi , Thank you for reaching out to the Microsoft Community Forum.

Based on your scenario, we created two DAX measures to count unique products that are active in both Element1 and Element2 during a given period. One gives the total per month and the other shows the count per day, based on overlapping date intervals. This accurately captures products active in both intervals, detecting overlaps across time ranges. Everything is visualized cleanly using the Calendar table without needing direct relationships.

Please check the attached .pbix file for your reference.

Thank you.

[KossFrance · Accepted solution]

Works like a charm 

Thanks a lot

[johnt75]

I think you can use

Unique Products =
VAR MinDate =
    MIN ( 'Calendar'[Date] )
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR ProdElement1 =
    CALCULATETABLE (
        VALUES ( Element1[Product ID] ),
        Element1[Start Date] <= MinDate,
        Element1[End Date] >= MaxDate
    )
VAR ProdElement2 =
    CALCULATETABLE (
        VALUES ( Element2[Product ID] ),
        Element2[Start Date] <= MinDate,
        Element2[End Date] >= MaxDate
    )
VAR Result =
    COUNTROWS ( INTERSECT ( ProdElement1, ProdElement2 ) )
RETURN
    Result

[amitchandak]

@KossFrance , Do not join the Date table. Then it would easy to use between join. Only join product table. 

Not for selected month or date this measure will work 

m1  = Countx(filter(Element1, Element1[start date]<= Max(date[Date])

&& Element1[end date]>= Max(date[Date]) ), Element1[Product Id]) 

same measure for element 2

m2  = Countx(filter(Element1, Element1[start date]<= Max(date[Date])

&& Element1[end date]>= Max(date[Date]) ), Element1[Product Id]) 

Change measure as per need