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

How to Cumulative Distinct Count with Filters

Hello All, I'd really appreciate some help as I am pulling my hair out trying to figure out what's wrong. I am trying to accumulate a count of distinct project numbers. In the table of detailed data, the totals come out differently than the cumulative count of a formula.

 

Cumulative Total ASP Wins IBE =
CALCULATE(DISTINCTCOUNT(QBProject[Project #]),
FILTER(ALLSELECTED('QBProject'),
'QBProject'[Offering] = "IBE_ASP" && 'QBProject'[Date Created] <= MAX('QBProject'[Date Created])))
 
This results in 27 less than the detailed data shows. I cannot find any common thread as to what is being excluded. I do have a calendar table with a full range of dates for 20 years, with the Date column marked in the pbix file. Could there be a problem if the QBProject (fact table) does not contain the full range of dates?
 
I know this isn't much to go on as I cannot share our data, but does anyone have any suggestions from this little bit of information?

 

3 REPLIES 3
Shelley Member
Member

Re: How to Cumulative Distinct Count with Filters

Okay, I figured out how to make it work, but still have one more question.

Here's what I did:

CALCULATE(
    [Total ASP Wins IBE],
    FILTER(
        CALCULATETABLE(
            SUMMARIZE('RA_Daily_Calendar'[Fiscal_YearMonth]),
            ALLSELECTED('RA_Daily_Calendar')),
        ISONORAFTER('RA_Daily_Calendar'[Fiscal_YearMonth], MAX('RA_Daily_Calendar'[Fiscal_YearMonth]), DESC)
))
    
Where Total ASP Wins IBE = CALCULATE(DISTINCTCOUNT(QBProject[Project #]),
'QBProject'[Offering] = "IBE_ASP")
 
However, now it plots all the way to the end of our fiscal year because we have some goal data that goes out that far. How can I get it to plot just to the current fiscal year-month? I have a flag field on my calendar that I was trying to use as a filter, but was unable to get it to work in the summarize table expression:
Flag: YTD or Older =
IF('RA_Daily_Calendar'[Date] <=
LOOKUPVALUE('RA_Daily_Calendar'[Date], RA_Daily_Calendar[Date], MAX('OrdersView'[Line_Creation_Date])),
1,0)
Community Support Team
Community Support Team

Re: How to Cumulative Distinct Count with Filters

Hi @Shelley ,

More details will be helpful.

If it is convenient, could you share your data sample and your desired output so that I could understand your scenario better and have a test on it?

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: How to Cumulative Distinct Count with Filters

Hi @Shelley ,

Have you solved your problem?

If you have solved, could you please share your solution or always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please your sample data and your desired output so that we could understand your scenario better.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.