Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |