Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am working on a dashbord for repeated order rate (ROR) for time period. At first, the time period is form the first date of the month - 100 days, and I need to display these values for time period (every month's first date - 100 days).
I've been google and looking a lot aand nothing seems to work for me.
I need to calculate a some values - total order count, total unique customer count, customer count with single order, customer count with multiple orders.
For dates, I used separate Dates table, where I calculated first date for every month (column name is
The I have Orders Table, which contains - Order Date, Order ID, Customer ID
I am try to create measures like this :
* Total Order count =
* Total Customers =
* Customers with single order =
The Table I need fill up, looks lsomething like this:
Any suggestion to help me solve this problem would be greatly appreciated.
Some Notes:
* When I created Mesures, which calculated same valus, but only for each month, the formulas worked fine, but when I tryed to adjust them for Date Period, something is noth working.
Try replacing your DATESINPERIOD calls with
DATESINPERIOD('Dates'[Date],MIN('Dates'[_First_date_of_the_month]), -100, DAY)
Thanks for the suggestion, but this is still not working for me.
I replaced DATESINPERIOD calls, but it seems that now the values are calculated only for the first date of the month, and not for the entire period... 😞
you could make sure that the dates in period is returning the correct dates by amending the measure to return a countrows of the last_x_days variable. that might help to identify where the problem is
I checked - countrows of the measure last_x_days is returning 100.
and the "start date" and "end date" is matching for each period.... soooo it looks like the problem is with calculations for Order count.
which columns form the relationship from your Date table to the Orders table?
Is the Date table marked as a date table?
What other filters are on your visual? Which other columns are in the visual?
Thank you for the recommendation to chechk if Date table is market as date table.
Seems like that was part of the problem, and now at least, "Total order count" and "Total customer" are calculated correctly, but "Customer with single order" and "Customer with mulltiple orders" are calculated incorectly - both solutins (the one I wsa using, and the one you suggested) are returning identical results.
I have thought and searched a lot - is there some multi-step solutinons (multiple measures) which could solve these issues?
You can use DAX Studio to test what is being returned for a sample date by running
DEFINE
VAR DateFilter =
TREATAS ( { DATE ( 2022, 9, 1 ) }, 'Date'[Date] )
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Orders'[Customer ID] ),
"@Total orders", [Total Order count]
),
DateFilter
)
that might give an idea as to whether the total order count measure is returning expected results
I test the code taht you provided.
The returned table conatains two columns - Customer id and @Total order. The Customer ID values are those Customer, who has made order in given date (2022,9,1 - in this case), and in the @Total orders columns are number of orders in last 100 days for each of those Customers who has mada a order in 2022,9,1.
The relationship is between "Date" from Dates table and "Order date" from Orders table.
There are no filter to be simpler.
And in the visual, I am using "_First_date_of_the_month" as first column (for first dates of the month), and then comes "Order count", "Total Customer", "Customer count with single order" and "Customer count with mulltiple orders".
And I will chech if Dates table is marked as date table. Thanks for the suggestion.
This is probably not causing the problem but I noticed that you are using SUMMARIZE to add a calculated column, which is not a good idea. Normally you should use SUMMARIZE to only return the static values and then wrap that in an ADDCOLUMNS to perform the calculations, there's a SQLBI article about it.
However in this case you don't really need to add the column at all, you could rewrite it like
* Customers with single order =
COUNTROWS (
FILTER ( VALUES ( 'Orders'[Customer ID] ), [Total Order count] = 1 )
)
Maybe this is not causing the problem at the moment, but its something to bear in mind.
Thank you,
I will bear in mind your recommendation.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |