cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## 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!

1 ACCEPTED SOLUTION
Community Support

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 6
Helper I

Thanks! Works great 🙂

Community Support

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.

Helper I

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)

Community Support

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.

Helper I

Sure! I hope this is more clear.

Community Support

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors