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

amitchandak's avatar
amitchandak
Super User
1 year ago

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