Help With DAX calculated column finding the earliest Date?

[GEN3Electric]

I'm building a sales Dashboard out of a report that I'm getting from Servicetitan.com my Saas CRM. The report has 3 columns that are interesting me but is missing one that I need and can be calculated. The columns are:

[opportunity #] Not unique in this report it can contains groups of Estimates.

[Estimate #] This # is unique in this report.

[Estimate Date] this is the date an estimate was created. Estimates on the same opportunity can have different dates.

<MISSING>

[Opportunity Date] I could use the earliest [Estimate Date] assoated with the [opportunity #] as the [Opportunity Date]. Is there a funstion for this?

EXAMPLE: My data look like this and I want to calculate the last column. For all of the Estimate in Opportunity 001 I need to see them on the date the opportunity was created. 

[opportunity #]            [Estimate #]                    [Estimate Date]             Calculated [Opportunity Date]

001                                     001                              1/1/2016                         1/1/2016

002                                     002                              1/1/2016                         1/1/2016

001                                     003                              1/2/2016                         1/1/2016

003                                     004                              1/5/2016                         1/5/2006

004                                     005                              1/7/2016                         1/7/2016

001                                     006                              1/7/2016                         1/1/2016

Thank you for the help.

[GEN3Electric · Accepted solution]

Thank You Jahida, That looks like it worked. Here is a copy of the me imputting this into my dashboard. 

[jahida · Accepted solution]

No problem. You killed the pronounciation the first time, then corrected yourself wrong haha.

[jahida]

Try making a calculated column with the formula (make sure Estimate Date is the correct type):

Column = CALCULATE(MIN(Table1[Estimate Date]), ALL(Table1), Table1[opportunity #] = EARLIER(Table1[opportunity #]))

[GEN3Electric]

Hi Jahida, This deffinetly looks like it is taking me in the right direction. I feel good that it is possable 🙂 I think I'm missing something very small here. Can you see where I'm going wrong?

[jahida]

The formula I gave was in DAX, this looks like you're in Query Editor (language = M). In order for my formula to work, you'll need to get out of Query Editor, go to the Data view in the main PowerBI window, navigate to the correct table, and go to

Modelling -> New Column. Then you can enter my formula and make the adjustments as you did.

Hope that helps!

[GEN3Electric]

HI Jahida,

I'm new to this is there a reason someone would use M over DAX?

Is there a way to handle this same problem in the Query Editor (Mlanguage = M)?

I'm reviewing the different task in my report and I have used both the Query editor and the Data View to get new columns but have no idea why I'm useing different methods to make then needed colummns. You opened my eyes to this. 🙂 

[jahida · Accepted solution]

I'm not an expert on this (many other people on this forum are experts by comparison), but here's my take:

DAX I find much easier to write. It's flexible, in my experience executes quickly on refresh, and if you use measures it can be dynamic.

M is a bit harder to write, for me at least. However, any processing you do in M, although it may not necessarily be faster, is better for the size of your file because 1) you can filter out unnecessary information before it loads and 2) columns created in M or brought in straight from the source are stored more efficiently than ones created in DAX (I just learned that yesterday). Also, M can be faster by pushing calculations back into the database at times. This, in my experience, is very inconsistent, but at times a great speedup.

Basically for a user just starting up, I would recommend doing filtering in M and nearly everything else in DAX, unless your dataset is absolutely massive and size is a massive concern.

Many people in the forum will protest that anything other than a Date table and calculated measures in DAX is bad style, at some point it does come down to personal preference and an ease of use vs. efficiency trade-off.

I'm sure there is a solution to this problem in M. It would take me a while to write though since I'm much more comfortable in DAX.

[GEN3Electric · Accepted solution]

Thank You Jahida, That looks like it worked. Here is a copy of the me imputting this into my dashboard. 

[jahida · Accepted solution]

No problem. You killed the pronounciation the first time, then corrected yourself wrong haha.

[GEN3Electric]

I will be sure to review the video and get the name right in the future. Thank you again.