HELP WHIT MY DAX

[ArquimedesP]

Hi guys! I kind of new using Power Bi and stil have some issiues using DAX whit calculates columns.

I have this data:

NUMERO_CARPETA_NU	FECHA_MOVIMIENTO	SECTOR_SOLICITUD_ACTUAL	SECTOR_SOLICITUD_ANTERIOR	FECHA_SECTOR_ANTERIOR
1	5/6/2008	OYCE	L	26/5/2008
1	10/6/2008	AC	L	10/6/2008
1	26/5/2008	AC	AC	26/5/2008
1	10/6/2008	L	OYCE	5/6/2008
1	26/5/2008	L	AC	26/5/2008
10044	23/8/2017	R	BO	23/8/2017
10044	23/8/2017	BO	AC	24/7/2017
10044	6/9/2017	GG	R	23/8/2017
10044	6/10/2017	GG	AC	8/9/2017
10044	8/9/2017	AC	GG	6/9/2017
10044	6/10/2017	AC	GG	6/10/2017
10044	24/7/2017	AC	AC	24/7/2017

I want to add calculated columns like this:

NUMERO_CARPETA_NU	FECHA_MOVIMIENTO	SECTOR_SOLICITUD_ACTUAL	SECTOR_SOLICITUD_ANTERIOR	FECHA_SECTOR_ANTERIOR	FECHA SECTOR ANTERIOR	SECTOR ANTERIOR	FECHA SECTOR ACTUAL	SECTOR ACTUAL
1	5/6/2008	OYCE	L	26/5/2008	10/6/2008	L	10/6/2008	L
1	10/6/2008	AC	L	10/6/2008	10/6/2008	L	10/6/2008	L
1	26/5/2008	AC	AC	26/5/2008	10/6/2008	L	10/6/2008	L
1	10/6/2008	L	OYCE	5/6/2008	10/6/2008	L	10/6/2008	L
1	26/5/2008	L	AC	26/5/2008	10/6/2008	L	10/6/2008	L
10044	23/8/2017	R	BO	23/8/2017	06/10/2017	GG	24/07/2017	AC
10044	23/8/2017	BO	AC	24/7/2017	06/10/2017	GG	24/07/2017	AC
10044	6/9/2017	GG	R	23/8/2017	06/10/2017	GG	24/07/2017	AC
10044	6/10/2017	GG	AC	8/9/2017	06/10/2017	GG	24/07/2017	AC
10044	8/9/2017	AC	GG	6/9/2017	06/10/2017	GG	24/07/2017	AC
10044	6/10/2017	AC	GG	6/10/2017	06/10/2017	GG	24/07/2017	AC
10044	24/7/2017	AC	AC	24/7/2017	06/10/2017	GG	24/07/2017	AC

For "Fecha Sector Anterior" column i used this DAX= 

CALCULATE(MAX(VW_Movimiento_Solicitud[Fecha_Sector_Anterior]);ALLEXCEPT(VW_Movimiento_Solicitud;VW_Movimiento_Solicitud[Numero_Carpeta_Nu]))

For "Sector Anterior" column i used this DAX=

VAR FECHA_ANTERIOR = CALCULATE(MAX(VW_Movimiento_Solicitud[Fecha_Sector_Anterior]);ALLEXCEPT(VW_Movimiento_Solicitud;VW_Movimiento_Solicitud[Fecha_Sector_Anterior]))

RETURN

CALCULATE(SELECTEDVALUE(VW_Movimiento_Solicitud[Sector_Solicitud_Anterior]);Fecha_Anterior=(VW_Movimiento_Solicitud[Fecha Sector Anterior]))

For "Fecha Sector Actual" i used this DAX =

CALCULATE(MAX(VW_Movimiento_Solicitud[Fecha_Movimiento]);ALLEXCEPT(VW_Movimiento_Solicitud;VW_Movimiento_Solicitud[Numero_Carpeta_Nu]))

For "Sector Actual" i used this DAX=

IF(VW_Movimiento_Solicitud[Fecha_Movimiento]=VW_Movimiento_Solicitud[Fecha Sector Actual];VW_Movimiento_Solicitud[Sector_Solicitud_Actual];"") (This is the only dax i used that worked, others gave me a circular dependency error).

[kentyler]

So you have Carpetas, Dates, and Sectors

Are you trying to calculate the previous sector a carpeta was in (that is, where it was before it moved ) ?

[ArquimedesP]

Yes, that is correct Ken. I want to know the last sector a carpeta was in and the actual sector it's in. 

[kentyler]

I reduced your data to a small sample 
There are a couple of business rules you have to decide on. 
1) if 2 entries have the same date, can one be "previous" to the other. If it can, is that based on the order the records were entered ?
2) if the previous entry for a given record is for a day that also has 2 entries on the same day, which of the 2 entries should be selected, again, should it be based on the order the records were entered in ?

If you make those decisions, then I can write the DAX to select the values you want.

[ArquimedesP]

There are a couple of business rules you have to decide on. 
1) if 2 entries have the same date, can one be "previous" to the other. If it can, is that based on the order the records were entered ?

A- Correct, based on the order the data was entered. But I don't manipulate those entries, this data is automatically loaded from another database.
2) if the previous entry for a given record is for a day that also has 2 entries on the same day, which of the 2 entries should be selected, again, should it be based on the order the records were entered in ?

A- It should take the last entry that was entered into the system. When someone uploads the data to the system, it only takes the date (it does not take hours, minutes or seconds). So if there are 2 entries the same day, you should be able to select the last one entered.

I just want to show where that folder is currently and where it was before that.

[kentyler]

I understand... the question is when there are 2 rows with the same date, which one do you select to represent "before that'

[ArquimedesP]

I have not way to know that. Maybe a DAX o QUERY to take the same order that it comes from the data base.

[kentyler]

The strategy illustrated here https://www.burningsuit.co.uk/blog/2019/11/find-a-value-in-the-previous-row/  should work for you... for some reason I can't get it to work in my example database.