Previous date according filter

[ghetus]

Hello!
I have a problem with the calculation of the previous last active date or number (according to the filter).

Example:
I have column 'Date version' with values {24.07.2017; 31.07.2017; 07.08.2017}.
I would like to find the previous date according to selected 'Date version' from the filter.

If i select all:
* current '24.07.2017' - previous 'null'
* current '31.07.2014' - previous '24.07.2017'
* current '07.08.2017' - previous '31.07.2017'

If i select some values:
* current '24.07.2017' - previous 'null'
* current '07.08.2017' - previous '24.07.2017'

[TomMartens]

Hey,

assuming your column Date version is of datatype Date or DateTime you can use this DAX Statement to calculate the previous Date (the datatype is necessary, because its implicit possibility to be ordered)

Previous Date = 
CALCULATE(
	MAX('FactWithDates'[Date])
	,FILTER(ALL('FactWithDates'[Date]),'FactWithDates'[Date] < MAX('FactWithDates'[Date]))
)

What this Statement does, is basically this. It retieves the MAX value (the date) from all dates that are older than the current date.

The phrase "current" can be somewhat mindboggling, at least is has been for me.

Using expressions like FILTER() to change the current FILTER CONTEXT you think like this: an additional column or more columns is / are added to the current FILTER CONTEXT.

In this example you can imagine that there are two columns 'FactWithDates'[Date], one contains the value of the moment before the FILTER CONTEXT has been changed and the other column all the dates returned by ALL(). Then the condition is used to reduce the dates to all the dates smaller than the value of original value. MAX() has been used, because of the facht, that there are more rows than just one.

Hope this helps

[ghetus]

Thank you Tom for your response

'Current' - is the selected value from filter. All the columns are date's datatype.

I tried your formula, but it doesn't work like i want. Lower, are some examples.

Your formula:

TomMartens = 
CALCULATE(
 MAX(Forecast[Date version].[Date])
 ;FILTER(ALL(Forecast[Date version]); Forecast[Date version] < MAX(Forecast[Date version]))
)

My formula:

My = 
 var 
 PreviousVerion = CALCULATE(MAX(Forecast[Date version].[Date]);filter(Forecast;Forecast[Date version]<EARLIER(Forecast[Date version])))
 return
 PreviousVerion

Result:

[img 1]

[img 2]

Returning to my question, i would like that in [img 2] in column 'My', for value '07.08.2017', will be equal to '24.07.2017' - namely previous value from selected.

[TomMartens · Accepted solution]

Hey,

here you will find a little example

Now the adjusted measure considers the SELECTED DataVersions

Here is the measure (I couldn't resist and just slightly changed the name of the new measure ;-))

TomMartens Adjusted = 
var currentSelection = CALCULATE(MAX('Table1'[DateVersion]))
return
 MAXX(ALLSELECTED('Table1'[DateVersion]),
	 IF('Table1'[DateVersion] < currentSelection, 'Table1'[DateVersion], BLANK())
 )

Hope this helps

[ghetus]

Thank you very much Tom