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 leavin...
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.

     

     

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.

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

        Sure! I hope this is more clear.