cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Identify Last value comparing current date vs previous date

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

Capture1.PNG

 

Expect Table with adding columns:

If change region compare before month yes or not

if yes getting previous Region

 

 

Capture2.PNG

 

Regards and thank you so much!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Identify Last value comparing current date vs previous date

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User VI
Super User VI

Re: Identify Last value comparing current date vs previous date

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Helper II
Helper II

Re: Identify Last value comparing current date vs previous date

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:

Capture4.PNG

 

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

 

Capture3.PNG

 

Regards ant thanks to follow up my post.

Highlighted
Super User VI
Super User VI

Re: Identify Last value comparing current date vs previous date

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

 

 

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Identify Last value comparing current date vs previous date

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors