Forum Discussion
Distinct Count by Year
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:
5 Replies
- Greg_Deckler
Community Champion
Perhaps:
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(ALL('Calendar'),'Calendar'[Plan Year Ending'] = MAX('Calendar'[Plan Year Ending]),
"__Date",'Calendar'[Date]
)
)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!
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!
- DataZoe
Microsoft Employee
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]))
