Forum Discussion
Find max date which is less than a specified date
I have a list of transactions for individual members, which has a date when each transaction occurred. I need to find the most recent date for a member which is less than a specified date. For example, if I have the following data
| MemberNo | Date |
| 1 | 1/1/2000 |
| 1 | 29/3/2012 |
| 1 | 28/1/2018 |
| 1 | 3/2/2020 |
| 2 | 23/6/2000 |
| 2 | 7/12/2001 |
| 2 | 8/8/2008 |
| 2 | 1/1/2020 |
and I need to find the most recent date for each member which is less than the 31st of January 2020, I would expect to get
for member 1: 28/1/2018
for member 2: 1/1/2020
I can't think of how to use max with filter? I'm trying this
MAX(FILTER('Member Financial'[Date], 'Member Financial'[MemberFundId] = 'Member Fund'[FundId]))
but it's erroring on me... help please 🙂
Assuming slicer us coming from Date calendar
max date =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(MAX('Member Financial'[Date]),'Member Financial'[Date]< _max)//OR
max date =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(MAX('Member Financial'[Date]),filter(all('Member Financial'),'Member Financial'[Date]< _max))//OR With date calendar -- This one should be the way
max date =
var _max = maxx(allselected('Date'),'Date'[Date])
return
calculate(MAX('Member Financial'[Date]),filter(all('Date'),'Date'[Date]< _max))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/Hi,
Please try this measure to show daygap:
DayGap = DATEDIFF(SELECTEDVALUE('Table'[Date]),SELECTEDVALUE('Date'[Date]),DAY)Then create a measure to calculate the most recent date:
Most Recent Day = var SummarizeTable = SUMMARIZE('Table','Table'[MemberNo],'Table'[Date],"DayGap",[DayGap]) var FIlterTable = FILTER(SummarizeTable,[DayGap]>0) var MinMeasure = MINX(FIlterTable,[DayGap]) return CALCULATE(MAX('Table'[Date]),FILTER(FIlterTable,[DayGap]=MinMeasure))The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
2 Replies
- amitchandak
Super User
Assuming slicer us coming from Date calendar
max date =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(MAX('Member Financial'[Date]),'Member Financial'[Date]< _max)//OR
max date =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(MAX('Member Financial'[Date]),filter(all('Member Financial'),'Member Financial'[Date]< _max))//OR With date calendar -- This one should be the way
max date =
var _max = maxx(allselected('Date'),'Date'[Date])
return
calculate(MAX('Member Financial'[Date]),filter(all('Date'),'Date'[Date]< _max))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/ - v-gizhi-msft
Community Support
Hi,
Please try this measure to show daygap:
DayGap = DATEDIFF(SELECTEDVALUE('Table'[Date]),SELECTEDVALUE('Date'[Date]),DAY)Then create a measure to calculate the most recent date:
Most Recent Day = var SummarizeTable = SUMMARIZE('Table','Table'[MemberNo],'Table'[Date],"DayGap",[DayGap]) var FIlterTable = FILTER(SummarizeTable,[DayGap]>0) var MinMeasure = MINX(FIlterTable,[DayGap]) return CALCULATE(MAX('Table'[Date]),FILTER(FIlterTable,[DayGap]=MinMeasure))The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi