Forum Discussion

ghetus's avatar
ghetus
Frequent Visitor
9 years ago
Solved

Previous date according filter

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.0...
  • TomMartens's avatar
    TomMartens
    9 years ago

    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's avatar
ghetus
Frequent Visitor
9 years ago

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's avatar
TomMartens
Super User
9 years ago

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's avatar
    ghetus
    Frequent Visitor
    9 years ago

    Thank you very much Tom