Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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'
Solved! Go to 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
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
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.
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
Thank you very much Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |