Get the most recent value in one table filtred by date in another table

[MSFun]

I have the following tables :

I want to have the most recent product (based on ProductLine_Date) with the associated amount in the sales table filtered by the situation date (Situation_Date) which must be less than or equal to the calendar date included in the filter (in a power report bi)

=> If user select 30/06/2024 as filter date in power bi, he get the most recent ProductLine (based on ProductLine_Date) for each Product and the associated amount in F_Sales

For exemple:

The SQL query is like that :

I want to do it with DAX in the cube.

[MSFun]

Here, the SQL database script for the sample, and a power bi  ihave created (import mode), it's a simple reporting file to do, as shown in screenshot :

GitHub - MSBIFun/Sample

[Anonymous]

@foodd Thanks for your contribution on this thread.

Hi @MSFun ,

I updated the sample pbix file(see the attachment), please check if that is what you want.

Maxdate = 
 VAR _sdate =
    SELECTEDVALUE ( 'Situation'[Situation_Date] )
RETURN
    CALCULATE (
        MAX ( 'Situation'[Situation_Date] ),
        FILTER ( ALLSELECTED ( 'Situation' ), 'Situation'[Situation_Type] = "SIT" )
    )

Production Date = 
VAR _sdate =
    SELECTEDVALUE ( 'Situation'[Situation_Date] )
RETURN 
    IF (
        _sdate = [Maxdate],
       EOMONTH(_sdate,0)+1,
        BLANK ()
    )

Amnt = 
VAR _sdate =
    SELECTEDVALUE ( 'Situation'[Situation_Date] )
RETURN
    IF (
        _sdate = [Maxdate],
        CALCULATE ( SUM ( 'F_Sales'[RMN] ), ALLSELECTED ( 'Situation' ) ),
        BLANK ()
    )

Best Regards

[MSFun]

@Anonymous Thank you for your reply. I may have expressed myself badly, or thought it's simple to integrate the solution in the existant model with other filters ... it doesn't work, I think I missed something. so to give all the details, I updated a power bi report with the 3 filters to apply: 1 filter on the calendar 1 filter on the type of situation: SIT or POS 1 filter on Sub (Sub contains several products) I want to have the most recent product of the shoosen Sub in filter (based on ProductLine_Date) with the associated amount in the sales table filtered by the situation date (Situation_Date) which must be less than or equal to the calendar date in the filter ( Year + month) filtered by the situation type (SIT or POS).

SQL DB script (Schema+Data), SSAS Cube solution + Power BI are here :

MSBIFun/Sample (github.com)

Thank you for your help

[foodd]

Hello @MSFun, and thank you for sharing a question with the Community.  Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.