Count distinct months of distinct years

[Anonymous]

Hello World,

Could you help me?

I'm new in power BI and I didn't find the answer for my question on the internet

I Need to calculate a quantity of months have in my sheet.

For example, in 2019 I have five months (Aug, Sep, Oct, Nov and Dec), and in 2020 I have more five months (Jan, Feb, Mar, Apr, May) = Total of 10 months. When i make "distinctcount(Months)" the system count 12 months, but don't have Jun and Jul in my sheet.I want to know why that happens.

And in Aug of 2020, I don't know how count one new month.

Could you help with me these two questions?

Thankful

[v-xuding-msft]

Hi @Anonymous ,

You could try this firstly:

Count Months = CALCULATE(DISTINCTCOUNT('Table'[Month]),ALLEXCEPT('Table','Table'[Year]))

 

[Anonymous]

Firsting, tnks so much for your help

unfortunately this formula keeps returning me 12 months, not 10

If you want to take a look at my database, the link is below

https://drive.google.com/drive/folders/1G1vJfbr-Nd-ZrkTm736q3Vy8G2oJeu9U?usp=sharing

[ryan_mayu]

@Anonymous 

maybe you can try add a month column

month=month(table[date])

Then distinctcount(month)

[ryan_mayu]

@Anonymous 

Please share the sample data and the expected output.

[Anonymous]

Hi Ryan,

Follow Bellow link of the sheet

https://drive.google.com/drive/folders/1G1vJfbr-Nd-ZrkTm736q3Vy8G2oJeu9U?usp=sharing

I need that the count of the months returns 10

the way I'm doing it is returning 12, and in my database there are no months June and July

so far I am using the same formula as in the comment below:

Count Months = CALCULATE(DISTINCTCOUNT('Abonos'[Date].[Month]),ALLEXCEPT('Abonos',Abonos[Date].[Month]))

I don't have a DCalendar sheet, so it doesn't make sense to make a formula using another sheet

Tnks for your Help

[mahoneypat]

You could add a calculated column to your table with this formula

YearMonth = FORMAT(Table[Date], "YYYMMM")

and then make a measure that does a DISTINCTCOUNT of that column.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nvprasad]

Hi,

I believe you are applying distinct count on the column which have 10 months instead of calendar table (if you have any).

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad