Forum Discussion

LLT1021's avatar
LLT1021
Icon for Helper I rankHelper I
3 years ago
Solved

Finding previous value with measure

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!

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

     

     

6 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      LLT1021
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        v-zhangti
        Icon for Community Support rankCommunity Support
        3 years ago

        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.