Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
This request for help involves two tables (snippets of both are below):
dimDate
CustomerPurchases
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).
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.
Bump - is anyone able to help, please?
@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
@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.
The end result should be in the form of the below screenshot.
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).
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |