MINIMUM Order Date

DAX DAX Calculations
1

Hi,
I have a task to display a table with customers details and beside the first date the customer made orders, I am to create a measure that returns the next available date
I used the MIN (DATE) which is not correct. It should be simple but I am trying to master this language ASAP.
I am to only show the date where a customer made an order [Next Order Date ] beside the first order.

image
image1183Ă—865 117 KB

Super Store Model.pbix (850.0 KB)
I want the report to show only the customers where Next Order Date is not blank
Thank you so very much.

2

Hi @UpwardD.

Here’s one way.

The DAX you have returns the minimum (first) order date.

First Order Date = MIN ( Orders[Order Date] )

To get the second order date, just get the minimum of those after the first order date.

Second Order Date = 
VAR _FirstOrderDate = MIN ( Orders[Order Date] )
VAR _SecondOrderDate = CALCULATE( MIN ( Orders[Order Date] ), Orders[Order Date] > _FirstOrderDate )
RETURN
_SecondOrderDate

Finally, to just show those customers with second order dates, you can use the filter pane.

eDNA Forum - Minimum Order Date-1
eDNA Forum - Minimum Order Date-1421Ă—920 35 KB

Is something like this what you’re looking for?
Greg
eDNA Forum - Minimum Order Date.pbix (849.4 KB)

3

Your original post doesn’t make it clear if you are looking for the next order date in the filtered period, or the next order date including and after the filtered period.

So, I have included both.

Step 1 - you were using a naked column reference in your original table, I changed that to a measure:

 Order Date Measure = 
FIRSTDATE( Orders[Order Date] )

Step 2 - the measure that respects the filter (will not show order dates after the filter period)

Next Order Date = 
VAR FirstOrder = 
    IF( Orders[Order Date Measure] = BLANK(), 
        FIRSTDATE( 'Date'[Date] ), 
        Orders[Order Date Measure] )
VAR NextOrder = 
    CALCULATE( 
        FIRSTDATE( Orders[Order Date] ),
            Orders[Order Date] > FirstOrder)
RETURN
    NextOrder

Step 3 - the measure that goes past the filter

Next Order Date (dates including and after filter) = 
VAR FirstOrder = FIRSTDATE( Orders[Order Date] )
VAR StartPeriod = 
    IF( FirstOrder = BLANK(), 
        FIRSTDATE( 'Date'[Date] ), 
        DATEADD( FirstOrder, 1, DAY ) )
VAR EndPeriod= LASTDATE( ALL( 'Date'[Date] ))
VAR NextOrder = 
    CALCULATE( 
        FIRSTDATE( Orders[Order Date] ),
            DATESBETWEEN( 'Date'[Date], 
                StartPeriod,
                EndPeriod))

RETURN
    NextOrder

image
image769Ă—172 4.73 KB

eDNA solution - Super Store Model - showing next order.pbix (846.0 KB)

NOTE: this actually builds on the solution provided to you by @Rens in this post:

4

It errored out on the server.

image
image2217Ă—894 77.7 KB

Thank you

5

Hi @Heather,
Thanks for the reply. I did applied the steps that @Rens gave. We were never able to run it against the server. It timed out everytime I try to work with it. Even now, the Next Order Date produce the same resukt for all the rows:

image
image1947Ă—1568 306 KB

Thank you so very much. I anticipate your reply.

6

Hi @upwardD. It runs fine on my machine … I wouldn’t have posted it otherwise. Can you reboot, just launch Power BI Desktop, and try again? (I’d like to check if it might be a memory issue on your machine …).
Greg

7

@upwardD,

I did not have any problems with my solution either - and as you can see, the solutions provided by @Greg and I are very similar.

What version of Desktop are you using @upwardD ?
I noticed in your original demo model that you weren’t using a measure for the “first date” value - do you have any measures that are working?

8

Hi @Heather, thanks for getting back. I am using the latest version of Power BI desktop. I got not run it on a test server either. I had to put the logic into SQL to overcome the issue.
Thank you so much for your support.

9

I’m glad you found a solution - but you may want (as you have time) to go over your model again for performance improvements.

It seems that there might be some other issues with your model if this measure is not working.

10

Hi @upwardD, did the response provided by @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

11

Hi @upwardD, we’ve noticed that no response has been received from you since the 22nd of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

1 Like