Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Count months with non-blank data

Hello friends,

I have created a dynamic "Headcount" measure, that shows the number of men-Months/Quaters/Years dynamically depending on what the user chooses. For example if an employee works full-time for a full year, then if a user chooses March, he/she would see 1 men-month; if the selection is Q2, then the value will be also 1, but in terms of men-quarter, etc.

My calculation is: [YearlyHeadcount]*12/DISTINCTCOUNT(CalendarTable[Year-Month])

The challenge is that distinctcount also counts month from the calendar table that do not yet contain any data.

 

If now, in May, a user chooses Q2, I would like to count two months (months with data - April, May) and not 3 months.

 

How do I accomplish this?

 

Thank you

 

Michael

Need Help

5 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Adjust your formula to something like this...

    Months =
CALCULATE (
    DISTINCTCOUNT ( 'Calendar Table'[Year-Month] ),
    'Calendar Table'[Date] <= TODAY ()
)

    Hope this helps! :smileyhappy:

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks Sean but I "less than today()" is not good for me, since the data might take a while to update

      I need to actually check how many months are there WITH DATA.

      Please help

      Thanks

      Michael

      • v-caliao-msft's avatar
        v-caliao-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Anonymous,

         

        You can count your table rows and then except blank row, so that you can get non blank row.

        CountRows = COUNT(Table2[Month])

        CountBlank = COUNTBLANK(Table2[Amount])

        CountNonBlank = Table2[CountRows]-Table2[CountBlank]

         

        Regards,

        Charlie Liao

    • wzkoral's avatar
      wzkoral
      Icon for Advocate II rankAdvocate II
      6 years ago

      What kind of relation do Yuo have beetwen date_table and facts . I am asking beacause have some isue with this formula. Looks tha same but in every period (Year, quater mounth ) i have total result .