Forum Discussion

aamulhim's avatar
aamulhim
New Member
3 years ago
Solved

Count Months that contain Values

I need to count the months that contain values to calculate the average spending.   The attached photo shows the months that contain values.   The target is to get the average spending like this ...
Need Help
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    3 years ago

    Hi aamulhim 

     

    Try these:

    with a separate dates table

    =
CALCULATE (
    DISTINCTCOUNT ( dates[month] ),
    FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)

    no dates table

    =
CALCULATE (
    DISTINCTCOUNT ( 'dates'[month] ),
    FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)

    if none works, please share a sample data (not an image) and  your expected result.

danextian's avatar
danextian
Icon for Super User rankSuper User
3 years ago

Hi aamulhim 

 

Try these:

with a separate dates table

=
CALCULATE (
    DISTINCTCOUNT ( dates[month] ),
    FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)

no dates table

=
CALCULATE (
    DISTINCTCOUNT ( 'dates'[month] ),
    FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)

if none works, please share a sample data (not an image) and  your expected result.

aamulhim's avatar
aamulhim
New Member
3 years ago

Thank you so much Dane. It works.

 

Best regards,