How to Cumulative Distinct Count with Filters

[Shelley]

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?

[Shelley]

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)

[v-piga-msft]

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

[v-piga-msft]

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