Min/Max Date and finding the 2nd Latest Date

[MarkDGaal]

Hi Everyone, I'm dealing for the first time with a dataset that appends [Export_Date_&_Time] every time an export is run from the datasource. Illustrating: 

ID         Export_Date_&_Time    250 more data elements/fields....
01A       01/01/2015 24:00:00      
02B       01/01/2015 24:00:00      
01A       12/12/2015 24:00:00     
02B       12/12/2015 24:00:00     

I'm creating a bunch of measures using the [Export_Date_&_Time] values. For example:

MAXDate = MAX(REF_Opportunites[Export_Date_&_Time])
MINDate = MIN(REF_Opportunites[Export_Date_&_Time])
AbsoluteMAXDate = CALCULATE(MAX(REF_Opportunites[Export_Date_&_Time]), ALL(REF_Opportunites))
AbsoluteMINDate = CALCULATE(MIN(REF_Opportunites[Export_Date_&_Time]), ALL(REF_Opportunites))

Then a bunch of columns that will help me filter my visuals:

IsEarliestExportDate = IF(REF_Opportunites[Export_Date_&_Time]= [AbsoluteMINDate], "Earliest", "Later")
IsLatestExportDate = IF(REF_Opportunites[Export_Date_&_Time] = [AbsoluteMAXDate], "Latest", "Not Current")
IsEarliestandLatestExportDates = IF(REF_Opportunites[Export_Date_&_Time] = [AbsoluteMINDate] || REF_Opportunites[Export_Date_&_Time]= [AbsoluteMAXDate], "Yes", "No")

I have 2 questions...

Why is it that if I try to use [MaxDate] (vs [AbsoluteMaxDate]) in my "IsLatestExportDate" IF statement that I don't get the same result? With [Max Date] everything in my column changes to "Latest"? I would like to be able to use [Max Date] in the event that I want to install a slicer/filter on my dashboard that uses the [Export_Date_&_Time] Field. In this senario, if I choose any other option in the slicer other than the [AbsoluteMaxDate] it would render all the visualizations blank (assuming "IsLatestExportDate" is a visual/page level filter).

How would I go about creating a measure/column that would allow for the selection of the Maximum date AND the period directly prior to the max...? (I've tried to use LATEST here but it gave me an error about having duplicate date values in a column)..... 

If there is a better approach here I would love to hear it, I'm finding Date and Time functions in DAX extremely frustrating. Also, before you ask the [Export_Date_&_Time] field is not generated at any pre-defined interval so I don't believe any of the Time Intelligence functions will be of use.

[v-caliao-msft · Accepted solution]

Hi Mark,

The reason why using MAXDate always return the latest, is caused by the Calculated column context. When using the MAXDate measure, it is calculated under the current calculated column context fields, which only owns the current row of the table, so when doing the MAX function within the MAXDate Measure, it always compared with the single value itself, that is way it always return the true result with the latest.

To workaround this, simply put the measure expression into the If expression, which should be:

IsLatestExportDate = IF(REF_Opportunites[Export_Date_&_Time] = MAX(REF_Opportunites[Export_Date_&_Time]) , "Latest", "Not Current")

While when using AbsoluteMAXDate, using ALL function as a filter within calculate functions, it actually removes the calculated column row context filter, which makes the MAX function to work with the entire column, this time the measure would gain the actual Max date, so the result is correct.

For the date that just prior to the Max date, take a try with the formula below:

Secondlateset = CALCULATE(MAX(REF_Opportunites[Export_Date_&_Time]), filter(REF_Opportunites, REF_Opportunites[Export_Date_&_Time]<>MAX('Table'[date])) )

Here is the result from the testing:

Regards,

Charlie Liao

[ShrikantKhanna]

Hi @v-caliao-msft

Need your assistance.

I have a large dataset where I have to find how many days has it been between Last sales date and second last sales date. I first summarized the primary sales table into a new table to create list of all distinct complainy names / IDs I have made sales to.

Then calculated First CONFIRMED sales date (first ever) by filtering out the confirmed sales only (and not considering any that was turned down for some reason) using filter on ClientName / ID and Sales ="Confirmed". Similarly, I calculated the Last Confirmed Sales date . All of this happened correctly.

SUMMARY = SUMMARIZE('ALLSALES','ALLSALES'[clientid],'ALLSALES'[clientName])

FIRST CONFIRMED SALES DATE = CALCULATE(MIN('ALLSALES'[SALESDATE]),FILTER(ALL('ALLSALES'),'ALLSALES'[SALES STATUS]="CONFIRMED"),FILTER(ALL('ALLSALES'),'ALLSALES'[clientid]='SUMMARY'[CLIENTID]))

LAST CONFIRMED SALES DATE = CALCULATE(MAX('ALLSALES'[SALESDATE]),FILTER(ALL('ALLSALES'),'ALLSALES'[SALES STATUS]="CONFIRMED"),FILTER(ALL('ALLSALES'),'ALLSALES'[clientid]='SUMMARY'[CLIENTID]))

SECOND LAST CONFIRMED SALES DATE = CALCULATE(MAX('ALLSALES'[SALESDATE]),FILTER(ALL('ALLSALES'),'ALLSALES'[SALES STATUS]="CONFIRMED"),FILTER(ALL('ALLSALES'),'ALLSALES'[clientid]='SUMMARY'[CLIENTID]),FILTER(ALL('ALLSALES'),'ALLSALES'[SALESDATE]<>MAX('SUMMARY'[LAST CONFIRMED SALES DATE])))

When I do SECOND LAST CONFIRMED SALES DATE by the above expression I am getting the same exact result as LAST CONFIRMED SALES DATE.

[MarkDGaal]

@v-caliao-msft Thanks, I knew there was something I wasn't understanding something about how measures evaluate. I've corrected "IsLatestExportDate" to use the MAX() function rather than the [MAXdate] or [AbsoluteMAXDate]. So we are left with:

IsLatestExportDate = IF(REF_Opportunites[Export_Date_&_Time] = MAX(REF_Opportunites[Export_Date_&_Time]) , "Latest", "Not Current")

So my issue is this..... I'd like to place [IsLatestExportDate] as a visual level filter to some of my visuals to make sure they are showing the most current data. I thought that the exclusion of the ALL() function from IsLatestExportDate would cause the calcuated column to re-evaluate what is "Latest" when a slicer/filter selected an [Export_Date_&_Time] that is < the maximum date in the column. However, it appears that using a filter/slicer on the report/page has no impact on the calculation of Max inside of the [IsLatestExportDate] calculated column.

Displaying [MAXDate] as a value in a Table visual on the report correctly updates so I'm confused as to why filtering by the Max() funciton doesn't do the same inside of a calculated column...

To this end, is there anyway for my calculated column to re-evaluate what is "Latest" if someone wanted to exclude the most recent [Export_Date_&_Time] from the filter/slicer....?

EDIT: Correction Suggested to your previous post to change "<>MAX('Table'[date])" to "<>MAX(REF_Opportunites[Export_Date_&_Time])"

[v-caliao-msft]

Thanks for the reminding.

The reason why using [MAXDate] would update the data, is because the column data changed when using the slicer, measures would calculate the data the time when it is used, so for this situation, when using [MAXDate] with the modified column, it will calculate the updated value.

For calculated column, the data are fixed, which is calculated just after finishing its definition, so the value won’t update.

And based on what I know, there is no way to re-calculate the value in a calculated column for a second time.

[MarkDGaal]

@v-caliao-msft hummmm so in summary if measures are the only thing that can recalculate upon filtering and column context only owns a single row and not the table it (there by making use of a measure in a calc column useless) it seems like a perfect reason for Microsoft to allow Measures to be used as filters on [Fields] under advance filtering and then placing the measure into the "greater than" or "less than" boxes.

Your solution for finding the 2nd largest date worked wonderfully (wish I had thought of that), I'll leave this question open incase someone else has an alternative approach to my issue and if not mark that the solution.