Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
faint127
Frequent Visitor

Get Max Value per category (Power Query)

Hey Guys!

 

So, i have this two tables

 

Screenshot_2.jpg

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

 

Screenshot_4.jpg

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! 

 

 
 
1 REPLY 1
camargos88
Community Champion
Community Champion

Hi @faint127 ,

 

Check this file as an example: Download PBIX 

 

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

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors