topic DAX for measure to determine 6 month trailing sum of first 6 months of customer orders in DAX Commands and Tips

DAX for measure to determine 6 month trailing sum of first 6 months of customer orders

jflipse — Tue, 07 Apr 2020 19:48:19 GMT

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!

Re: DAX for measure to determine 6 month trailing sum of first 6 months of customer orders

Greg_Deckler — Tue, 07 Apr 2020 20:42:26 GMT

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-TITHW/m-p/434008

Re: DAX for measure to determine 6 month trailing sum of first 6 months of customer orders

jflipse — Fri, 10 Apr 2020 19:05:03 GMT

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!

Re: DAX for measure to determine 6 month trailing sum of first 6 months of customer orders

Greg_Deckler — Fri, 10 Apr 2020 22:41:30 GMT

Great, glad you got it! I agree that simpler is better. Complexity is evil! 🙂