Forum Discussion

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

Please help me to solve this issue>

Thank you in advance.

Tips and Tricks

Tutorial Requests

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.

5 Replies

Ashish_Mathur
Super User
3 years ago
Hi,

It will depend up on the ganularity of your data. Share the download link of the PBI file.
- aamulhim
  New Member
  3 years ago
  Hi
  
  Thank you for your reply. I have attached a screenshot from the data view, I hope that will help.
  - Ashish_Mathur
    Super User
    3 years ago
    Hi,
    
    An image does not help. Please share the download link of the PBI file.
danextian
Super 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
  New Member
  3 years ago
  Thank you so much Dane. It works.
  
  Best regards,