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

[Olivier789]

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:

Result:

 

[v-shex-msft]

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

[Olivier789]

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.

[v-shex-msft]

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