cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

DistinctCount of unique id with date filters and "interrupted timeline"

I'm looking for a solution to report the history of accounts from a separate SQL. To limit the load, I take an initial snapshot that I complete at the end of each week/month with accounts where a change took place (based on different conditions). It is possible that a week/month goes by without any change to a specific account. So that account is not transferred to the separate SQL. In the end I want to show in a graph per week/month how many customers and prospects there are for that week/month (incl the possibility to click-trough/drill down, to see which accounts are selected). Which formula should I use to achieve this?

 

Example:

Power BI Example.png

Result:

 

Power BI Example2.png 

 

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: DistinctCount of unique id with date filters and "interrupted timeline"

Hi @Olivier789,

 

Maybe you can try to use below measure, it will auto drill to correspond level.

 

unique count =
CALCULATE ( DISTINCTCOUNT ( table[company] ), VALUES ( Table[date] ) )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
New Member

Re: DistinctCount of unique id with date filters and "interrupted timeline"

Unfortunately I get the same result for every month (4) with:

 

unique count =
CALCULATE ( DISTINCTCOUNT ( table[company] ), VALUES ( Table[date] ) )

Am I doing something wrong?

 

The problem lies in the fact that not every ID is mentioned in every week / month. So I have to check for each id the last mentioned value and recreate it for every week/month to report it into a graph with a timeline as x-axis.

Highlighted
Community Support
Community Support

Re: DistinctCount of unique id with date filters and "interrupted timeline"

Hi @Olivier789,

 

Can you please share some sample data to test and coding formula?

 

>>The problem lies in the fact that not every ID is mentioned in every week / month.

I think you need one calendar to handling incomplete date range.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors