Count months with non-blank data

[Anonymous]

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

[Sean]

Adjust your formula to something like this...

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

Hope this helps! 

[wzkoral]

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 .

[Anonymous]

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]

@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

[Anonymous]

Thanks @v-caliao-msft,

My challenge is not to count months without any data,

COUNTBLANK() as I understand, counts the number of ROWS with blank values in a column in the source table.

I need to count how many months (not rows) are there (a month can have many rows)

I have overcame this by calculating in PowerQuery but it would be nice to know how to do this in DAX

Thanks

Michael