Forum Discussion

rosscortb's avatar
rosscortb
Post Patron
7 years ago
Solved

COUNT Monthly Headcount

Hello,

 

This should be so simple but I just can't get it right with the multiply combinations I've tried.

 

I am looking to create column with total row count for each month but for some reason its giving me the overall total or sometimes it gives me 1.

 

 

Regards

Ross

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi rosscortb

    Create a column like this

    count = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[year],Table1[month number]))

     

    Best Regards

    Maggie

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    rosscortb are you adding column or measure?

     

    Preferred/best practice is to add calendar table in your table and use that for any time intelligence related calculations.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi rosscortb

    Create a column like this

    count = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[year],Table1[month number]))

     

    Best Regards

    Maggie

      • rosscortb's avatar
        rosscortb
        Post Patron
        7 years ago

        Hi Maggie

         

        Sorry hope you don't mind me asking. I used your solution on another calculation which returns the monthly headcount filtering on payroll - "Babcock Weekly"

         

        This works but what I am looking for is it return the previous months value.  I've tried PREVIOUS MONTH and DATEADD but had no success. 

         

        CALCULATE(COUNTROWS('DB Headcount'),'DB Headcount'[Payroll Area] <> "Babcock - Weekly",ALLEXCEPT('DB Headcount','DB Headcount'[Date],'DB Headcount'[Month by number]))
         
        Regards

        Ross