Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure for calculating appointments

I have the following measure for which counts the number of appointments for a given day:

CALCULATE(COUNT(Appointment[Date appointment]),
FILTER(Appointment, Appointment[Status] = "Planned" || Appointment[Status] = "Occurred"))

 

This produces the following table when combined with a Calendar[Date] column:

 

I want another column which shows the sum of appointments for the coming 6 months, for example, the 4-1-2021, which would sum the values from 4-1-2021 til 3-7-2021. However, the Appointment[Booking date] should not be larger than in this case 4-1-2021.

 

Does anyone know how to achieve this?

 

Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Assume active join of date table is with Appoint date

     

    Rolling 6 = CALCULATE(Calculate(Countrows(Table), filter(Table, Table[Booking Date] <=Max('Date'[Date]))) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),6,MONTH))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This unfortunately does not seem to provide the desired result:

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        Please try:

        TEST =
CALCULATE (
    COUNT ( Appointment[Date appointment] ),
    FILTER (
        Appointment,
        Appointment[Status] = "Planned"
            || Appointment[Status] = "Occurred"
    )
)
        Rolling 6 = 
CALCULATE (
    CALCULATE (
        [TEST],
        FILTER ( 'Calendar', 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), 6, MONTH )
)

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data