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]
MAX (TableC[Table B Date]),
TableC[Category] = EARLIER ( TableC[Category] )
&& TableC[Date] <= CurrentDate ) )
Amount A =
var CurrentDate = Table C[Date With Data (Hidden Aux)]
MAX ( TableB[Amount])+0,
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.
Proud to be a Super User!
Click here to read more about the October 2021 Release!
Explore the latest tools,training sessions,technical expertise, networking and more.
Mark your calendars and join us for our next Power BI Dev Camp!