Forum Discussion
Help With DAX calculated column finding the earliest Date?
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.
Thank You Jahida, That looks like it worked. Here is a copy of the me imputting this into my dashboard.
No problem. You killed the pronounciation the first time, then corrected yourself wrong haha.
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.
8 Replies
jahidaImpactful Individual
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 #]))
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!