Count Months that contain Values

[aamulhim]

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 (114,225 / 7 = 16,318).

Please help me to solve this issue> 

Thank you in advance. 

[danextian · Accepted solution]

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]

Thank you so much Dane. It works.

Best regards,

[Ashish_Mathur]

Hi,

It will depend up on the ganularity of your data.  Share the download link of the PBI file.

[aamulhim]

Hi 

Thank you for your reply. I have attached a screenshot from the data view, I hope that will help.

[Ashish_Mathur]

Hi,

An image does not help.  Please share the download link of the PBI file.