DAX Last Date - Other field

[Jonete]

Hello:

I need help with an issue i’m not able to solve by myself.

I’m working on a model about storage. I have an article table and a movements table.  An article migth be moved from one store to another, and i woul like to get the store of the last movement for each article.

To get that, I´m creating a table:

CALCULATETABLE (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CALCULATETABLE (
                IFMSAL_ARTICLES,
                SUMMARIZECOLUMNS ( IFMSAL_MOVEMENTS [Article] )
            ),
            "Date",Calculate(Max(IFMSAL_ MOVEMENTS [MovementDate])),
            "Store", CALCULATE ( MAX ( IFMSAL_ MOVEMENTS [MovementStore] ) )
           
        ),
        "articulo", IFMSAL_ ARTICLES [Article],
        "Store",[Store],
        "Date",[Date]
        
    )
)

But I know it’s not right because what i get is the maximun store number not the store of the las movement.

Any help would be appreciated.

The model looks like this:

Movements table:

Date	Article	Store
01/01/2018	Article 1	Store A
02/01/2018	Article 2	Store A
03/01/2018	Article 3	Store A
04/01/2018	Article 4	Store A
05/01/2018	Article 1	Store A
06/01/2018	Article 1	Store B
07/01/2018	Article 2	Store A
08/01/2018	Article 3	Store B

Articles table:

Article	Description
Article 1	Article Description 1
Article 2	Article Description 2
Article 3	Article Description 3
Article 4	Article Description 4

And what i would like to obtain is:

Article	Store
Article 1	Store B
Article 2	Store A
Article 3	Store A
Article 4	Store B

[Anonymous · Accepted solution]

Hi @Jonete,

You can try to use below measure to get last store group by article.

LastStore =
VAR temp =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [Article], "LastDate", MAX ( [Date] ) ),
        "Store", LOOKUPVALUE (
            Table1[Store],
            Table1[Article], [Article],
            Table1[Date], [LastDate]
        )
    )
RETURN
    MAXX ( FILTER ( temp, [Article] = SELECTEDVALUE ( Table1[Article] ) ), [Store] )

Regards,

Xiaoxin Sheng

[lzorn]

Hi @Anonymous,

I have tried your code in power BI with the given examples from the main question. However it does not seem to work. I have changed the code by swapping the comma's for semicolons to remove the errors that power BI gave. When there are no more error's there isn't any result in the calculated colum.

Can you help me further with this?

Below the code I have used:

LastStatus =
VAR temp =
ADDCOLUMNS (
SUMMARIZE ( 'Maint VW_DPR2_CheckLists'; [System]; "LastDate"; MAX( 'Maint VW_DPR2_CheckLists'[StartDate]) );
"Status"; LOOKUPVALUE (
'Maint VW_DPR2_CheckLists'[Status];
'Maint VW_DPR2_CheckLists'[System]; [System];
'Maint VW_DPR2_CheckLists'[StartDate]; [LastDate]
)
)
RETURN
MAXX ( FILTER ( temp; [System] = SELECTEDVALUE ( 'Maint VW_DPR2_CheckLists'[System] ) ); [Status] )

Kind regards and hope to hear from you soon,

Léjon

[Jonete]

Thank yoy very much Xiaoxin Sheng. 

It works great!!!!!!!!!!!!!!!