First order / date

[bvanderwatt]

Good Day 

I am trying to add a calculated column or measure to identify the first time an order was placed for a specific product. 

I have a date table connected to my product table. 

But I'm not sure how to identify the first date based on my data set. 

Any help will be much appreciated 🙂 

[TheoC]

Hi @bvanderwatt 

Without knowing your data model / structure, I cannot provide an exact solution. However, if you go to this link (https://community.powerbi.com/t5/Desktop/Calculating-date-time-differences/m-p/2505774), I have provided a solution to something very similar.  Just go straight to step 2 which is "Calculated column to return the Min DateTime by Station ID".

_MinDatebyProduct = 

VAR _Product = 'Table'[Product]
VAR _MinDate = MINX ( FILTER ( ALL ( 'Table' ) , 'Table'[Product] = _Product ) , 'Table'[Date] )

RETURN

_MinDate

The above should be generic and you can just change the name of your Table and Column to complete your solution 🙂

Hope this helps!

Theo

[bvanderwatt]

Hi. Thank you very mcuh for your reply. I've added the below calculated columns for the first transaction date and 6 months from the first transaction date. 

However, I'm not sure how to calculate the sales between these two dates. Any chance you can assist? 

[TheoC · Accepted solution]

Hi @bvanderwatt 

If I'm understanding what you're after, you should be able to use:

Total Between Dates = CALCULATE ( SUM ( 'Table'[Amount] ) , FILTER ( 'Table' , 'Table'[Date] >= 'Table'[FirstTransactionDate] && 'Table'[Date] <= 'Table'[FirstTransactionDate +6M] ) )

Hope this helps 🙂

If the post has been solved, make sure to close it off by marking the comments as solution(s) so that others can also use it! 🙂

Cheers again!

Theo

[bvanderwatt]

Thank you so much for helping me, I really appreciate it. 

Last question, hopefully 🙂 

I need to add a filter to my "first transaction" calc, because it's also taking into account my budget values. How could I restrict it to only look at the column called "inv+order?"

FirstTransactionDate =

CALCULATE (

FIRSTDATE ( 'Combined Sales'[InvoiceDate] ),

FILTER ( 'Combined Sales', 'Combined Sales'[StockCode] = EARLIER ( 'Combined Sales'[StockCode] ) ))

[TheoC]

Hi @bvanderwatt 

You need to integrate ALLEXCEPT which filters out everything else.  

As much as I want to continue to help on this post, we have moved far away from what the original post was about and if others are searching for similar assistance, they won't be able to identify the solution on here. 

I'm more than happy if you want to add a new post or send a private message, and I can assist further that way. But we need to close this post off because it's well beyond the initial issue. 

I hope you understand and please don't think I'm being rude... I'm just conscious of others who will look to use this post for their assistance. 

Look forward to helping further through one of the other two means presented. Please ensure to close this post off by marking the relevant comment(s) as a solution.

Cheers,

Theo

[bvanderwatt]

No problem. I've accepted the solution and created a new post. I've also tagged you in it 🙂