Forum Discussion

KossFrance's avatar
KossFrance
Frequent Visitor
1 year ago
Solved

Calculate IDs regarding multiple date intervals condition

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 ...
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    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's avatar
    KossFrance
    1 year ago

    Works like a charm 

    Thanks a lot

johnt75's avatar
johnt75
Super User
1 year ago

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