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 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

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.

4 Replies

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    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
      Frequent Visitor
      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 

  • 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