Get Max Value per category (Power Query)

[faint127]

Hey Guys!

So, i have this two tables

I want to create a table (Table C) witch results in Table A and Table B join (Date, Category).

It sounds easy, but i started using Power Query recently, so i still have some difficulties, especially in retrieving "Last Date With Data "in order to get the last amount reported by date / category.

I don't know if i managed to explain all correctly, but here is the DAX formula i've used to obtain succefully the two columns:

Date With Data (Hidden Aux) =
VAR CurrentDate = TableC[Date]
RETURN
CALCULATE (
MAX (TableC[Table B Date]),
FILTER (
Table C,
TableC[Category] = EARLIER ( TableC[Category] )
&& TableC[Date] <= CurrentDate ) )


Amount A =
var CurrentDate = Table C[Date With Data (Hidden Aux)]
return
CALCULATE (
MAX ( TableB[Amount])+0,
FILTER (
TableB,
TableB[Category] = EARLIER ( TableC[Category] )
&& TableB[Date] = CurrentDate ) )

Thanks in advance! 

[camargos88]

Hi @faint127 ,

Check this file as an example: Download PBIX 

I've used Power Query to merge em fill down the null values.

Ricardo