Identify Last value comparing current date vs previous date

[RicLup]

Hi Guys, I hope you can help me, I need identify if a user change the assignned región comparing last date vs previous date, creating a column with yes or not depending the movement and create other column to have the previous Región in case change it.

For Example

Original Table

Expect Table with adding columns:

If change region compare before month yes or not

if yes getting previous Region

Regards and thank you so much!!

[mahoneypat · Accepted solution]

Here are two expressions for your columns.

Change Region =
VAR __thisregion = Regions[Region]
VAR __thisdate = Regions[Date]
VAR __prevdate =
    CALCULATE (
        MAX ( Regions[Date] ),
        ALLEXCEPT ( Regions, Regions[Name] ),
        Regions[Date] < __thisdate
    )
VAR __prevregion =
    CALCULATE (
        MAX ( Regions[Region] ),
        ALLEXCEPT ( Regions, Regions[Name] ),
        Regions[Date] = __prevdate
    )
RETURN
    IF ( OR ( __thisregion = __prevregion, ISBLANK ( __prevregion ) ), "No", "Yes" )


Previous Region =
VAR __thisregion = Regions[Region]
VAR __thisdate = Regions[Date]
VAR __prevdate =
    CALCULATE (
        MAX ( Regions[Date] ),
        ALLEXCEPT ( Regions, Regions[Name] ),
        Regions[Date] < __thisdate
    )
VAR __prevregion =
    CALCULATE (
        MAX ( Regions[Region] ),
        ALLEXCEPT ( Regions, Regions[Name] ),
        Regions[Date] = __prevdate
    )
RETURN
    IF ( __thisregion = __prevregion, BLANK (), __prevregion )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[RicLup]

You have right @mahoneypat , the problem was my Column Date had text format, sorry, changing the format the columns works perfect. Thank you very much.

[RicLup]

hi @mahoneypat 
Im adding the new columns that you shared me, but the result expect is different that show in my original post.

Shared Calculate Result:

Below I Show you the differences with the expect result.

Explain me better for the example, I need to identify when a person change the region comparing with the previous month in each row with yes(Change Region Column) and register the previous region(Previous Region Column)

This is the example, with the original table, the calculate columns you shared me and the expect result for every column

Regards ant thanks to follow up my post.

[mahoneypat]

That's odd.  In my pbix with your example data, I see your expected result, so there is something different in our two models.  Can you post the adapted expressions you are using with your Table/Column names?  Were all of the "Regions" table names replaced with your table name?

Regards,

Pat