Finding the max date row between date range and date range is dynamic.

[asanand]

Hello 

I am new to PowerBI and looking for one solution. I tired to find the solution in this forum and do a lot of google but not able to find anything related to this. 

Problem: 

There is a transaction table with multiple values for a transaction happened on different dates. Now we want to pick the max row as per the date range selected from the slicer. 

For eg: if user selects Jan 1 2017 till Jan 15 2017 then all the records which lie between these dates should be considered but we will pick the record with the max date from those records with in the above selected date range. 

Like if one value having Jan 7th as the max date we will pick 7th record and if another record has the max date of 1 we will pick the 1st record. Similiarly if any record having 100 records but the max date is 7th we will pick record with the date 7th and even if the records having 100 records with the date range going beyond Jan 15 2017, we will only consider records between the date range selected by the user. 

I hope I am able to well explain this problem. Following a example I tried to create for better understanding. Looking for the help from the community, I am all set and excited about powerbi 🙂

We would like to do something like this

T1	1-Jan-17
T1	2-Jan-17
T1	3-Jan-17
T1	4-Jan-17
T1	5-Jan-17
T1	6-Jan-17
T1	7-Jan-17
T2	1-Jan-17
T2	3-Jan-17
T2	4-Jan-17
T2	8-Jan-17
T2	15-Jan-17
T3	4-Jan-17
T3	6-Jan-17
T3	7-Jan-17
T3	26-Jan-17
T3	5-Feb-17
Selection: 1	1-Jan-17	7-Jan-17
T1	7-Jan-17
T3	7-Jan-17
Selection : 2	15-Jan-17	30-Jan-17
T2	15-Jan-17
T3	26-Jan-17

[v-shex-msft · Accepted solution]

Hi @asanand,

You can use below formula to get the max date of each type based on slicer:

Measure:
Max Date = 
var curr=LASTNONBLANK(Sheet4[Column1],[Column1])
Return
MAXX(FILTER(ALLSELECTED(Sheet4),[Column1]=curr),[Column2]) 

Regards,

Xiaoxin Sheng

[asanand]

@v-shex-msft Hello, This is not working with Direct Query. Is there a way to rewrite this which will work with Direct Query? 

Thanks 

[asanand]

I am able to enable the functions via options and its working now. Thanks for your help on this. Appreciate it!!!

[asanand]

Thank you for your help and though it returning me the correct date but when I am trying to sum the flags its not counting it correct and counting the values multiple time. This is what I have done, can you please take a look and advise what's wrong with this. Thanks 

YieldPassCount = VAR curr =
LASTNONBLANK (
Dashboard_MCS_YieldReports[Stop_Time],
Dashboard_MCS_YieldReports[Stop_Time]
)
RETURN CALCULATE(SUM(Dashboard_MCS_YieldReports[PassFlag]),FILTER(Dashboard_MCS_YieldReports,(
MAXX (
FILTER (
ALLSELECTED ( Dashboard_MCS_YieldReports ),
( Dashboard_MCS_YieldReports[Stop_Time] = curr )
),
Dashboard_MCS_YieldReports[Stop_Time]
))),GROUPBY(Dashboard_MCS_YieldReports,Dashboard_MCS_YieldReports[SerialNumber],Dashboard_MCS_YieldReports[AssemblyName]))

[asanand]

Thanks for the reply, I will check this and will let you know. 

[Habib]

Please use DatesBetween filter inside a calculate functon.