DAX / PowerBI Min/Max Date and finding the 2nd Latest Date

Question

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.

Answer 1

Q1. If you use a slicer on Export_Date_&_Time and filter dates less or greater than [AbsoluteMaxDate] you will get Not Current for every row in the IsLatestExportDate column. Even if you use [MAXDate] measure, something like this:

IsLatestExportDate = IF([MAXDate] = [AbsoluteMAXDate], "Latest", "Not Current")

You will get the same result since [MAXDate] is calculated using the rows filtered from the slicer, in this case the rows different than [AbsoluteMAXDate] so of course you will get always Not Current.

Q2. To get the previous date to the max date I've created a column and a measure that uses that column.

PreviousColumn = 
CALCULATE (
    LASTDATE ( REF_Opportunites[Export_Date_&_Time] ),
    FILTER (
        REF_Opportunites,
        [Export_Date_&_Time] < EARLIER ( REF_Opportunites[Export_Date_&_Time] )
    )
)

Then just create a measure to calculate the latest previous to the maximum date.

LatestPrevious =
LASTDATE ( REF_Opportunites[Previous] )

Let me know if this helps you or at least drives you in the right direction.

Answer 2

Q1: The reason why using [MAXDate] always return the latest when used inside of [IsLatestExportDate], 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 why it always return the "Lastest".

To get around this, one can use MAX(REF_Oppportunities[Export_Date_&_Time]) inside of the [IsLatestExportDate] calculated column (instead of a measure containing this formula). However, when you filter or slice your dataset there is no way (to my knowledge) to have your calculated column re-evaluate based on filtering the current filters applied; therefore, "Latest" will only identify the true MAX() of the entire column.

Q2: Because the LASTDATE() function can't work on a column with duplicated dates I created the following Calculated Column to find the 2nd most recent export date:

Is2ndLatestExportDate = IF(REF_Opportunites[Export_Date_&_Time] = CALCULATE(MAX(REF_Opportunites[Export_Date_&_Time]), filter(REF_Opportunites, REF_Opportunites[Export_Date_&_Time] <> MAX(REF_Opportunites[Export_Date_&_Time]))), "2nd Latest", "Not 2nd Latest")