Merge table by latest date and two conditions.

[mghafforzoda]

Hi. I need help merging two tables. I have SalesTable with Product [Code], [Date], [Shop] and StatusTable with Product[Code], [Date], [Shop] and [Status]. I want to get [Status] from StatusTable as a column in SalesTable. SalesTable continuous dates but StatusTable only has dates when [Status] changes. So if there was a record in SalesTable for [Date] "5/1/2020" in [Shop] "D" for [Code] "1234" I need to get its [Status]. In [Status] I need to find [Shop] "D", [Code] "1234" but there won't be [Date] "5/1/2020". In that case I need to check latest date, let's say it is "2/1/2020", and I will need to get [Status] for this date.

I really hope that I explained problem clearly. Any help will be appreciated.    

Example.pbix

[v-jayw-msft · Accepted solution]

Hi @mghafforzoda ,

Check the following formulas.

latest_date = 
var _lookup = LOOKUPVALUE(StatusTable[Date],StatusTable[Shop],SalesTable[Shop],StatusTable[Code],SalesTable[Code],StatusTable[Date],SalesTable[Date])
return
IF(ISBLANK(_lookup),CALCULATE(MAX(StatusTable[Date]),FILTER(StatusTable,StatusTable[Shop]=SalesTable[Shop]&&StatusTable[Code]=SalesTable[Code])),_lookup)

status = LOOKUPVALUE(StatusTable[Status],StatusTable[Code],SalesTable[Code],StatusTable[Shop],SalesTable[Shop],StatusTable[Date],SalesTable[latest_date])

I noticed that the date and code column are in different types between two tables in your pbix file, please make sure they are same type.

Result would be shown as below.

Best Regards,

Jay

[Pragati11]

Hi @mghafforzoda ,

Can you try following DAX:

Status Calc =

var datemax = MAX(StatusTable[Date])

var StatusMax = CALCULATE(MAX(StatusTable[Status]), StatusTable[Date] = datemax)

RETURN

MAXX(filter(StatusTable, StatusTable[Code] = SalesTable[Code] && StatusTable[Shop] = SalesTable[Shop] ),

StatusMax)

[mghafforzoda]

Hi. Thanks for your reply. Unfortuanlety it is not working. I get "0" in all rows.

[amitchandak]

@mghafforzoda , Please make the data type in both table the same. Date and Code do no have same data type in both tables.

In DAx you can create a new column

Column = maxx(FILTER(StatusTable, SalesTable[Date] =StatusTable[Date] && SalesTable[Code] =StatusTable[Code] && SalesTable[Shop] =StatusTable[Shop]),StatusTable[Status])

in power Query you can merge

if need create a new column in boh table in power query

key = [Date] & [code] & [Shop]

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[mghafforzoda]

Hi. Thanks for your reply. Unfortuanlety it is not working. I don't get Status for days that are not found in StatusTable[Date]. 

I suspect that problem is in "SalesTable[Date] =StatusTable[Date]" becuse we are using "=". I tried ">=" but it also doesn't give me correct answer.