Forum Discussion
Latest Status
Well, you could do this:
Status = VAR __maxDate = MAX('Stock Status Table'[Status Date]) VAR __stockKey = MAX('Stock Table'[Stock Key]) VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate) VAR __count = COUNTX(__table,[Status Date]) RETURN IF(__count >1 || ISBLANK(__count),"?",MAXX(__table,[Description]))If you add an Index column to your Stock Status Table (with the assumption that your rows are sorted/ordered by Date from least recent to most recent), then you could do this:
Status2 = VAR __maxDate = MAX('Stock Status Table'[Status Date]) VAR __stockKey = MAX('Stock Table'[Stock Key]) VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate) VAR __maxIndex = MAXX(__table,[Index]) VAR __table1 = FILTER(__table,[Index] = __maxIndex) RETURN MAXX(__table,[Description])
Sample data would really help but basically you are going to use MAX of your date then you just have to figure out the multiple entries on a single day. Perhaps adding an Index to your query?
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
Well, you could do this:
Status = VAR __maxDate = MAX('Stock Status Table'[Status Date]) VAR __stockKey = MAX('Stock Table'[Stock Key]) VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate) VAR __count = COUNTX(__table,[Status Date]) RETURN IF(__count >1 || ISBLANK(__count),"?",MAXX(__table,[Description]))If you add an Index column to your Stock Status Table (with the assumption that your rows are sorted/ordered by Date from least recent to most recent), then you could do this:
Status2 = VAR __maxDate = MAX('Stock Status Table'[Status Date]) VAR __stockKey = MAX('Stock Table'[Stock Key]) VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate) VAR __maxIndex = MAXX(__table,[Index]) VAR __table1 = FILTER(__table,[Index] = __maxIndex) RETURN MAXX(__table,[Description])