cancel
Showing results for
Did you mean:

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

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!

1 ACCEPTED SOLUTION
Super User
Great, glad you got it! I agree that simpler is better. Complexity is evil! 🙂

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
3 REPLIES 3
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

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!

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.