Finding previous value with measure

[LLT1021]

Hi all!

I have a question regarding previous values. 
I have a table with data about employees of a company like;

Name     Gender   Exit date          Date                Number of people leaving   Distance
X             F             01-05-2023     01-05-2023    1                                             -
X             F             01-05-2023     01-04-2023     0                                            5
Y             M                                   01-05-2023    0                                             10
Y             M                                   01-05-2023     0                                             10
I am interested in distance for the people leaving, but as you can see, when leaving (number people leaving = 1) there is no distance value, so I would like to obtain the value of the month before leaving, in this case for person X the value of distance at 01-04-2023. There is a filter in the final table displaying the info of leavers with 'Number people leaving'>0 and I am struggling with creating a measure that generates distance at the month before leaving and ignoring the number of people leaving filter.
Thanks for you help!

[LLT1021]

Thanks! Works great 🙂 

[v-zhangti]

Hi, @LLT1021 

You can try the following methods.

Distance Measure = 
Var _Previousdate=CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) )
Return
IF ( SELECTEDVALUE ( 'Table'[Exit date] ) <> BLANK (),
    CALCULATE ( SUM ( 'Table'[Distance] ),
        FILTER ( ALL ( 'Table' ),
            [Name] = SELECTEDVALUE ( 'Table'[Name] )
                && [Date]=_Previousdate ) ),
    BLANK ()
)

Measure = IF(SELECTEDVALUE('Table'[Number of people leaving])>0,1,0)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LLT1021]

Thanks a lot! This is already helping a lot. The only thing is that now the minimum of 'date' is taken in variable 'Previousdate' which does not always work as it should be the date before the exit date (there could be a lot more rows for person X with possibly other distance values, but the only relevant one is the one with date = exit date - 1 month)

[v-zhangti]

Hi, @LLT1021 

Can you provide more sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LLT1021]

Sure! I hope this is more clear.

[v-zhangti · Accepted solution]

Hi, @LLT1021 

You can try the following methods.

Previous date = 
Var _leavedate=CALCULATE(MAX('Fact Employee'[Date]),FILTER(ALL('Fact Employee'),[People leaving]=1&&[Name]=SELECTEDVALUE('Fact Employee'[Name])))
Var _previousdate=CALCULATE(MAX('Fact Employee'[Date]),FILTER(ALL('Fact Employee'),[Name]=SELECTEDVALUE('Fact Employee'[Name])&&[Date]<_leavedate))
Return
IF(SELECTEDVALUE('Fact Employee'[People leaving])=1,_previousdate,BLANK())

Result = CALCULATE(MAX('Fact Financial'[Distance]),FILTER(ALL('Fact Financial'),[Name]=SELECTEDVALUE('Fact Employee'[Name])&&[Date]=[Previous date]))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.