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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
azalazi
New Member

Power pivot-Calculated Field- to return value correspond max date before date in row of main table

I need to return value from table2 for max date before the Date in table 1, 

below table shows the illustration

 

Table1 (Main Table)

DateIDVALUE
30-06-20239999???   it should return X1
30-08-20239999???   it should return B6

 

Table2

DateIDValue
01-05-20239999Y6
15-05-20239999X1
10-07-20239999B6

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @azalazi ,

 

You can use Merge Queries.

Merge like the following.

vstephenmsft_0-1695888069445.png

 

vstephenmsft_1-1695888178872.png

vstephenmsft_3-1695888285994.png

 

Create a custom to filter the Date.1.

vstephenmsft_4-1695891738177.png

vstephenmsft_5-1695891767062.png

Filter out the nulls.

vstephenmsft_6-1695891775831.png

vstephenmsft_7-1695891787243.png

Group by as follows.

vstephenmsft_9-1695891865228.png

Expand the All Rows column.

vstephenmsft_10-1695891903180.png

Add another custom column to filter again.

vstephenmsft_11-1695891956816.png

vstephenmsft_12-1695891976466.png

 

vstephenmsft_13-1695891990327.png

Remove the unneeded columns and you get the result.

vstephenmsft_14-1695892004996.png

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @azalazi ,

 

You can use Merge Queries.

Merge like the following.

vstephenmsft_0-1695888069445.png

 

vstephenmsft_1-1695888178872.png

vstephenmsft_3-1695888285994.png

 

Create a custom to filter the Date.1.

vstephenmsft_4-1695891738177.png

vstephenmsft_5-1695891767062.png

Filter out the nulls.

vstephenmsft_6-1695891775831.png

vstephenmsft_7-1695891787243.png

Group by as follows.

vstephenmsft_9-1695891865228.png

Expand the All Rows column.

vstephenmsft_10-1695891903180.png

Add another custom column to filter again.

vstephenmsft_11-1695891956816.png

vstephenmsft_12-1695891976466.png

 

vstephenmsft_13-1695891990327.png

Remove the unneeded columns and you get the result.

vstephenmsft_14-1695892004996.png

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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