Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I am attempting to determine the 6 month trailing sum or product quantities and have been successful using these measures:
Qty AC = SUM( 'Orders'[Quantity] )
6MonthTrailingSumQty =
VAR RollingSum =
CALCULATE(
[Qty AC],
DATESINPERIOD( Date[BIDIM_DATE], LASTDATE( Date[BIDIM_DATE] ),-6, MONTH )
)
RETURN
RollingSum
Plotted...
With these relationships in the model:
So far, so good.
Things get interesting when I try to filter the 6 month trailing sum for only those customers who have not had previous orders i.e. are new customers for their first 6 months of activity using DATESBETWEEN:
6MonthTrailingSumQtyNewCustomers =
CALCULATE(
SUMX( 'Orders', 'Orders'[Quantity] ),
DATESINPERIOD( Date[BIDIM_DATE], LASTDATE( Date[BIDIM_DATE] ),-6, MONTH ),
DATESBETWEEN(
Date[BIDIM_DATE], 'Earliest Orders'[First Order Date], DATEADD( 'Earliest Orders'[First Order Date], 6, MONTH )
)
)
It is throwing an error in DATESBETWEEN that “A single value for column 'First Order Date' in table 'Orders' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.”
I understand the error, that it is unable to determine the first order date for a customer (Facility Name (ID))”, and am struggling with the syntax to get it to evaluate on each order.
In the end my goal is, for each date as I move forward in time by BIDIM_DATE, to check if the orders for that date are for a brand new customer in their first 6 months, and if so then to add the quantity of products in those orders to the total. And have that simultaneously as a trailing 6 month sum. The final step is to plot this trend over time to see the contribution of new customers in their first 6 months of life to the trailing sum over time (the first measure above).
Any thoughts on how to proceed would be greatly appreciated – thanks!
Solved! Go to Solution.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Thanks @Greg_Deckler for the suggestion! It was great food for thought. I like your approach and will keep it in mind moving forward. My ultimate resolution was much more basic, flagging the orders in the order table that were within 6 months of the initial order, then just filtering that way. Sometimes simpler is the way to go, but only after you bang your head on the wall for a bit until you see the solution. 😛
Thanks again!
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |