cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jflipse
Advocate III
Advocate III

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 plotted6 month trailing sum plotted

 

With these relationships in the model:

 

PBI Post 6MoTrailingSum inquiry 20200407.png

 

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

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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
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...

 

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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!

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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!