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" || Appo...
Need Help
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your respone Anonymous! 

       

      The Appointment[Booking date] should not be larger than the date that is displayed in the 'Date' column as shown below. Right now, this constraint is not in place, hence why the values in 'Rolling 6.2' are too large. Do you have any idea how to achieve this?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        Please try:

        Rolling 6 = 
CALCULATE (
    CALCULATE (
        [TEST],
        FILTER ( 'Appointment', 'Appointment'[Booking 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