Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
D_PBI
Post Patron
Post Patron

How to DISTINCTCOUNT all IDs from the earliest Date until the date specified in another column?

Hi,
This request for help involves two tables (snippets of both are below):
dimDate

D_PBI_0-1708017208637.png

 

CustomerPurchases

D_PBI_1-1708017345028.png

The relationship between the two tables is dimDate[Date] 1-* CustomerPurchases[Purchase Date].

I'm expecting the output to show in the following fashion. Ignore the red error indications - I've renamed the DAX so to hide sensitive column names.
The slicer has the Financial Year and Financial Quarter (as an expanable node) from the dimDate table.
The table visual has the Financial Year and Financial Quarter from the dimDate table. The 'Customer IDs' contains the measure I'm trying to create. The DAX present is my latest effort (although I've tried various efforts).

D_PBI_3-1708018268303.png

 

I would like the measure to count all DISTINCT CustomerPurchases[Customer ID] records from the earliest CustomerPurchases[Purchase Date] up until the dimDate[Max Financial Quarter Date].
For example, looking at the dimDate table, for Financial Year 2023/24 and Financial Quarter 2, the dimDate[Max Financial Quarter Date] is the 2024/01/31. Therefore I would like the 'Customer IDs' measure to DISTINCTCOUNT all Customer IDs from the earliest CustomerPurchases[Purchase Date] to the (and including) 2024/01/31.
This same logic should apply to each Financial Year and Financial Quarter, regardless if the slicer is used or not. It's the row context that should be ascertaining the dimDate[Max Financial Quarter Date] to use as the max date in the measure.

I hope this makes sense.
How do I acheive this?
Thanks.

3 REPLIES 3
D_PBI
Post Patron
Post Patron

Bump - is anyone able to help, please?

amitchandak
Super User
Super User

@D_PBI , You can use inplace of DISTINCT CustomerPurchases[Customer ID])

 

lastnonblankvalue(DimDate[Date], DISTINCTCOUNT(CustomerPurchases[Customer ID]))

 

or refer

 

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak thank you for your response. Unfortunately, it didn't work. I've spent another few hours searching the net attemtping to achieve my aim but without success.

The table I'm attempting to perform the cumulative DISTINCTCOUNT on is shown in the screenshot below - it's just a snippet of the entire table.

D_PBI_0-1708089305682.png

The end result should be in the form of the below screenshot.

D_PBI_1-1708089412837.png

The slicer visual contains the Year and Quarter from the dimDate table.
The table visual contains the Year and Quarter from the dimDate table and the following columns:
'cumulative measure' - this is the measure I need help in creating.
_minDisclosureDate - this is the earliest Disclosure Date in the Disclosures table above.
Min Financial Quarter Date - this is the start date for the row's Disclosures[Disclosure Date]'s Year and Quarter.
Max Financial Quarter Date - this is the end date for the rows Disclosures[Disclosure Date]'s Year and Quarter.

The relationship is dimDate[Date] 1-* Disclosures[Disclosure Date].


My latest DAX for the 'Cumulative attempt' measure is in the screenshot below (I've used 'Disclosures' in place of the actual table name).

D_PBI_2-1708090000099.png

 

Cumulative attempt =
VAR _earliestDisclosureDate = CALCULATE( MIN( Disclosures[_minDisclosureDate] ), ALL( Disclosures ) )
VAR _maxFinancialQuarterDate = MAX( Disclosures[Max Financial Quarter Date] )
VAR _result =
    CALCULATE(
        DISTINCTCOUNT( Disclosures[contactid] ),
        FILTER(
            ALL( Disclosures ),
            Disclosures[Disclosure Date] >= _earliestDisclosureDate
            && Disclosures[Disclosure Date] <= _maxFinancialQuarterDate
        )
    )

RETURN
    _result


What the 'Cumulative attempt' measure is suppose to calculating is to DISTINCTCOUNT Disclosures[contactid] where the Disclosures[Disclosure Date] is between the Disclosures[_minDisclosureDate] and the Disclosures[Max Financial Quarter Date].
You can see the Disclosures[_minDisclosureDate] is always the same (01/01/1970), but the Disclosures[Max Financial Quarter Date] will differ depending on the dimDate[Financial Year] and dimDate[Financial Quarter] value of the row's context.
So the 'Cumulative attempt' for Year 2023/24 Quarter 2 should be a DISTINCTCOUNT of Disclosures[contactid] from the earliest Disclosures[Disclosure Date] until the end date in the rows's Disclosures[Max Financial Quarter Date].
This would be the same calculation for each Year and Quarter combination. It should still perform even when the user uses the slicer visual to only showing certains years/quarters in the table visual.

The numbe being returned are present is 5889 where, when testing through SQL and our CRM, the correct number is 2488. That's a massive difference and I don't believe it's anything due to the DISTINCTCOUNT not counting unique contactids - althought I could be wrong - I don't know what's wrong.

Please help someone as this is holding me up??
Thanks.



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.