Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors