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

[jflipse]

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...

6 month trailing sum 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!

[Greg_Deckler]

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...

[jflipse]

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!

[Greg_Deckler · Accepted solution]

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