Forum Discussion
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
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!!
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
4 Replies
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
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.
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
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.
