Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Hi @faint127 ,
Check this file as an example: Download PBIX
I've used Power Query to merge em fill down the null values.
Ricardo