Distinct Count by Year

[Anonymous]

Hello,

I believe I just need a relatively easy fix, but I'm struggling to get there. Hoping somebody can help. 

I am able to find the distinct number of months of a given plan year (not calendar year), using the DistinctCount function; however, when I drill down from Plan Year to Month, the Distinctcount is now just 1 instead of keeping the distinctcount where it was before. I understand why this is happening but not sure how to correct it so that the distinct count of dates remains constant when drilling down. 

Right now my formula reads as: 

CALCULATE(DISTINCTCOUNT('Calendar'[Date]),'Calendar'[Plan Year Ending]). 

Any help would be much appreciated. Thank you!

[DataZoe]

@Anonymous I think you are trying to show the # months in your year even when you are just looking at one month?  If so, this may work for you!

Count Months in Year = if(ISINSCOPE('calendar'[Month]),
CALCULATE(DISTINCTCOUNT('calendar'[Month]),'calendar'[Month]),
DISTINCTCOUNT('calendar'[Month]))

[Greg_Deckler]

Perhaps:

COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(ALL('Calendar'),'Calendar'[Plan Year Ending'] = MAX('Calendar'[Plan Year Ending]),
"__Date",'Calendar'[Date]
)
)

[Anonymous]

Hi @Greg_Deckler, I think you are close, but the visualization now is showing the count of all rows, not just those in the current plan year. If you see the visual below, the tall line should be at 6 for January through June (short plan year) and it should be 12 for July through the following June. Instead it is showing all 18 months. Any suggestions? Thanks!

[Anonymous]

@Greg_Deckler  Ok so I found out the issue. There are 18 distinct months becuase there are 2 different plan years and they overlap by 6 months. So I also need to filter not only by plan year but also by the account. I'm still working on figuring out how to do so, but that is the last part here. Thanks!

[Greg_Deckler]

I'm just pure guessing on this stuff @Anonymous because no sample data and expected output to go off of. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I could make a suggestion with the formula because it only referenced one table but I have no clue where your account column is coming from or how it is related or what columns are in the visual, etc.