Forum Discussion
Filter MAX Value by second last Date
Hello,
I hope I can explain my problem so that you understand it. Otherwise, like to ask.
Following Problem:
I need the MAX ID for the second last Date.
I get the MAX ID for "Laufende Nummer" is the ID to identify the [saldo].
_TEST Kontostand Heute =
VAR BezugLaufendeNummer_ = [_Max Laufende Nummer]
RETURN
CALCULATE(SUM('f Buchungen Bank'[Saldo]);'f Buchungen Bank'[Laufende Nummer] = BezugLaufendeNummer_)
But now I need the MAX ID für "Laufende Nummer" by the date before yesterday.
I got the date for today: (for example 06.05.2020)
_Letztes Datum = LASTDATE('f Buchungen Bank'[Buchungstag])
The Date for yesterday: (for example 05.05.2020)
_Vorletztes Datum =
CALCULATE (
MAX('f Buchungen Bank'[Buchungstag]);
FILTER (
'f Buchungen Bank';
'f Buchungen Bank'[Buchungstag] <> MAX( ( 'f Buchungen Bank'[Buchungstag] )
)
))
So now I Have to filter the saldo for the second last date for the max ID (laufende Nummer). I tryed with the folowing code but it dosent work:
Measure = CALCULATE(MAX('f Buchungen Bank'[Laufende Nummer]);'f Buchungen Bank'[Buchungstag] = [_Vorletztes Datum])
I thin it's not a Probleme for somone that have more experience then me.
Many thanks for help.
5 Replies
- Greg_Deckler
Community Champion
p_fehrenbach - I'm having problems following things because there's no data to reference and probably because the tables and columns are in a different language than what I speak. But, generally if you need the second to last of something, find the MAX value of that thing. Then, find the MAX value of that thing again but filtering out the previous MAX value. Like
MAXX(FILTER('Table',[Thing] <> MAX('Table'[Thing])),[Thing])
If that isn't specific enough, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - amitchandak
Super User
p_fehrenbach , assuming the date is joined with the calendar
measure =
var _max =max('Date'[Date])
var _date =MAXX(FILTER(all('Date'),'Date'[Date]<_max),Table['Date'])))
return
CALCULATE(Max('Table'[ID]),filter(all('Date'),'Date'[Date] =_date)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Many thanks for the help.
I use a calender. When you specify ('Date'[Date]) you mean the date of the calender,right?
By the defintion of the VAR _date:
var _date =MAXX(FILTER(all('Date'),'Date'[Date]<_max),Table['Date'])))
Table['Date'] - I can't choose the date from the table (not the calender) :
_ = var _max = MAX(Calender[Date]) var _date = MAXX(FILTER(ALL(Calender);Calender[Date] < _max); xxx) return CALCULATE(MAX('f Buchungen Bank'[Laufende Nummer];FILTER(ALL(Calender);Calender[Date] = _date)))- amitchandak
p_fehrenbach , we trying to get a date in 'f Buchungen Bank' which below max date(The date joined with Date table ). We can use 'Date'[Date] if needed
