Why does a DISTINCTCOUNT of [date].[year] count nonexistent years?

[Joris_NL]

Example file 

Hi,

So I already solved it by adding a YEAR column to work with, instead of the DATE column with a ".[year]" extension.
(or in my language settings ".[jaar]" )

But I still wonder, why would a computer ever count distinct values by comparing the minimum and maximum? So 2015 up to 2024 makes 10 years indeed. But that's not counting distinct values!

Note that 2016 2017 2018 are entirely absent in the data.

[Joris_NL]

Thank you for asking the AI.

1. Yes

2. Wrong. It does count missing years.

3. This is the interesting one. The data type simply is 'Date', nothing wrong with that. But if you put such a date column on an x-axis (as 'continuous' , not as ' categorical'), Power BI somehow shows all years/months/days in between your dates, even without any data. So somewhere in the background, a ' date'-type column has missing dates created, which are indeed counted with DISTINCTCOUNT.   

4. No relationships apply.

5. No MIN or MAX applies. Only DISTINCTCOUNT. 

[v-shex-msft]

Hi @Joris_NL ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Joris_NL]

Added an example file in the original post.

But it should work in every file with a date-type column and a DISTINCTCOUNT measure (if you have some dates/months/years missing of course).

[v-shex-msft · Accepted solution]

HI @Joris_NL,

AFAIK, these fields that extension behind the date field are generated by auto date time features.
Power bi will generate a hidden calendar table to generate date hierarchy, relationship mapping for time intelligence functions.
When you directly invoke and calculate on these expand fields, they may get the different values from your original table records.

You can take a look at the official document to know more about this.
Auto date/time in Power BI Desktop - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[johnbasha33]

@Joris_NL 

It seems like you're encountering an unexpected behavior when counting distinct values in Power BI, particularly with regards to the counting of years. Here are a few points to consider:

1. **Distinct Count Calculation**: The distinct count function in Power BI is designed to count the unique occurrences of a value within a column. It does this by scanning all the values in the column and eliminating duplicates. However, it does not consider the chronological sequence of the values; it only looks at the unique values themselves.

2. **Missing Years in Data**: If certain years are entirely absent in your data, the distinct count calculation will still count the years that are present. It won't generate or add missing years to the count. This behavior is consistent with how distinct counting functions typically operate.

3. **Column Type**: Ensure that the column you're using for counting distinct years is formatted as a date or has a data type that Power BI recognizes as a date. This ensures that Power BI treats the values in the column as dates and performs the distinct count calculation correctly.

4. **Data Modeling**: Check your data model and relationships to ensure that they are set up correctly. Sometimes, incorrect relationships or data modeling can lead to unexpected results in calculations.

5. **Aggregation Functions**: If you're using aggregation functions like MIN or MAX in your measures, ensure that they are appropriate for your calculation requirements. In this case, it seems like you're encountering unexpected behavior with the MIN and MAX functions in the distinct count calculation.

By verifying these points and ensuring that your data model, column types, and calculations are set up correctly, you should be able to resolve the issue with distinct count calculations in Power BI. If the problem persists, you may need to further investigate your data and calculation logic to identify any potential issues.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!