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
Hi,
I’ve been struggling with this issue for the last couple of hours. Perhaps you can help me out with it.
I have an ORDER table with order dates and customer ID’s, this is a simplified version of that table:
Order date | Customer ID |
Jan 1 2018 | AA1 |
Jan 2 2018 | AA1 |
Jan 3 2018 | BB2 |
Feb 1 2018 | AA1 |
Feb 1 2018 | CC3 |
Feb 1 2018 | DD3 |
Feb 1 2018 | AA1 |
I also have a separate date table that is marked as a date table, and which is linked to this ORDER table.
I’ve tried creating the following four measures:
What I’m struggling with is that I want to show these numbers in a report, and also want to show the Month over month increase/decrease for each of these segments. Any help is greatly appreciated!
Thanks
Bas
I loaded your example data as table1 into a pbix. The following measures give what you want
Distinct Customer Count = DISTINCTCOUNT ( Table1[Customer ID] )
Two Plus Orders = CALCULATE ( [Distinct Customer Count], FILTER ( Table1, CALCULATE ( COUNTROWS ( Table1 ) ) >= 2 ) )
At least one order in L3M = CALCULATE ( [Distinct Customer Count], DATESINPERIOD ( Table1[Order Date].[Date], ENDOFMONTH ( Table1[Order Date].[Date] ), -3, MONTH ) )
Thank you for the quick reply @Anonymous!
Both formula's don't seem to work for me. But as they work perfectly from your screenshot I think this might has something to do with the rest of my model.
The two plus orders (segment 1) returns blanks, and the At Least One Order in L3M (segment 3) returns the same results as the distinctcount formula (segment 2).
For Segment 1 I've used:
Segment 1 = CALCULATE( CALCULATE( DISTINCTCOUNT('Sales Data'[Customer ID]), FILTER('Sales Data',CALCULATE(COUNTROWS('Sales Data') >= 2)) )
I've tried the same calculation by doing a sum of the amount of orders, but it won't work either. I've checked the data in Excel and there are plenty of customers who have ordered more than twice every month. Could this have anything to do with my date filters in my report? I don't have any page or report filters actived.
Segment 2 is just a distintcount
CALCULATE(DISTINCTCOUNT('Sales Data'[Customer ID]))
For Segment 3 I now have:
CALCULATE( DISTINCTCOUNT('Sales Data'[Customer ID]), DATESINPERIOD ( _Date[Date], ENDOFMONTH (_Date[Date]),-3,MONTH) )
_Date is my date table.
I don't understand what the .[date] part in your formula does, when I add it here things get broken. How should I interpret that?
Thanks!
Bas
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
83 | |
76 | |
64 |
User | Count |
---|---|
142 | |
112 | |
98 | |
98 | |
94 |