Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Need help with a count measure

Hi, I want a graph with the total number of dogs in my kennel each month. The issue I’m running into is that I’m using the Start_Date on the X axis, this is only giving me dogs that started in that month and not a total of dogs in the kennels at that time.

 

For example, I need it to show the total number of dogs receiving support per month:

March = 3 dogs were in our kennels

June = 4 dogs were in the kennels

August = 3 dogs were in the kennels

 

DogStart_DateEnd_DateSupport_Status
Max01/01/2024 Open
Molly01/02/2024 Open
Alba01/03/202415/06/2024Closed
Chester01/04/202410/07/2024Closed
Birdie01/08/2024 Open

 

What measure would show the total number of dogs being supported in each month please?

 

Thanks

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    Make sure that you have a date table, there's plenty of articles and videos on creating a proper date table.

    You can then use columns from your date table in the visuals, and create a measure like

    Dogs per month =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Start Date] <= MaxDate
            && (
                'Table'[End Date] > MaxDate
                    || ISBLANK ( 'Table'[End Date] )
            ),
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    Make sure that you have a date table, there's plenty of articles and videos on creating a proper date table.

    You can then use columns from your date table in the visuals, and create a measure like

    Dogs per month =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Start Date] <= MaxDate
            && (
                'Table'[End Date] > MaxDate
                    || ISBLANK ( 'Table'[End Date] )
            ),
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result
  • RichOB's avatar
    RichOB
    Icon for Post Partisan rankPost Partisan
    1 year ago

    Hi johnt75 , thanks for your measure. I've watched some videos on YouTube, but have an issue.

     

    I've made a calendarauto table. If I make the relationship join from the calendar table to the start date, it only gives me the count of dogs that started on those dates (as it should), whereas I want the count per month of all dogs active within the months between when they start and the end date. What am I doing wrong here, please?

     

    Thanks

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      1 year ago

      Make sure that your measure has the REMOVEFILTERS( 'Date' ) in it.

  • v-karpurapud's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi RichOB 

    Could you please confirm if your query have been resolved the solution provided by johnt75 ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

    Thank you