New column based on comparing previous records

[mihaita_baro]

Hi guys,

I am working on a dashboard where i have to report which training course was not renewed. 

My data set looks like below and i want to add a new column called:

- Renewed and another one called,but I have no ideea how to compare curent row records with next record

- Renewal In 2 months for those that are due to expire in 2 months from current date, 

CourseId	TRAINING::Course	USERS::FullName	TrainingDate	TrainingRenewByDate	OutOfDate	Renewed
1	Manual Handling	John Stone	29/04/2021	30/04/2022	1
1	Manual Handling	John Stone	22/04/2022	23/04/2023		1
2	Lone Worker	John Stone	23/04/2021	24/04/2022	1
2	Lone Worker	John Stone	21/04/2022	22/04/2023		1
3	Complaint training & conflicts resolution	John Stone	29/04/2021	30/04/2022	1
3	Complaint training & conflicts resolution	John Stone	21/04/2022	22/04/2023		1

[v-xiaotang · Accepted solution]

Hi @mihaita_baro 

" to compare curent row records with next record", you can use filter to get the record with earlier date and then the one with another date from the same CourseId, for example,

m1 = 
var _date=CALCULATE(MIN('Table'[TrainingDate]),ALLEXCEPT('Table','Table'[CourseId]))
return IF(MIN('Table'[TrainingDate])= _date,1)

m2 = 
var _date=CALCULATE(MAX('Table'[TrainingDate]),ALLEXCEPT('Table','Table'[CourseId]))
return IF(MIN('Table'[TrainingDate])= _date,1)

to find renewal In 2 months for those that are due to expire in 2 months from current date, you can use edate() to get the date 2 months from today, then use it to compare the renew date.

Best Regards,

Community Support Team _Tang

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

[mihaita_baro]

hi @v-xiaotang this is not working because it needs to compare if the course id and the name are the same.

How do i compare both fields in Dax. ?

[v-xiaotang]

Hi @mihaita_baro 

Thanks for your reply.

>>because it needs to compare if the course id and the name are the same.How do i compare both fields in Dax. ?

You need to compare course id and users fullname? But these two columns are not the same in the sample data. Could you please give an example based on the sample data? Thanks

Best Regards,

Community Support Team _Tang

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

[mihaita_baro]

Hi @v-xiaotang  

Maybe i didint explained my self very good. The data looks like in the above just that for one CourseID i have multiple Users not just one.

So i need the latest record for an user and a course to be called as Renewed. 

I am working on this for couple of weeks and still cant get my head arround this

[v-xiaotang]

Hi @mihaita_baro 

Thanks for reply.

In this scenario, put "'Table'[USERS::FullName]" into allexcept(),

measures:

m1 =
var _date=CALCULATE(MIN('Table'[TrainingDate]),ALLEXCEPT('Table','Table'[CourseId],'Table'[USERS::FullName]))
return IF(MIN('Table'[TrainingDate])= _date,1)

m2 =
var _date=CALCULATE(MAX('Table'[TrainingDate]),ALLEXCEPT('Table','Table'[CourseId],'Table'[USERS::FullName]))
return IF(MIN('Table'[TrainingDate])= _date,1)

Best Regards,

Community Support Team _Tang

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

[mihaita_baro]

Hi

I am still getting the same result, it's 1 for the olderst training date no matter the courseID or Name.

this is my measure, 

Renewed =

var _date=CALCULATE(MAX('TrainingRecord'[TrainingDate]),ALLEXCEPT(TrainingRecord,'TrainingRecord'[CourseId],TrainingRecord[Training»Course_Attendees»USERS::FullName]))

return IF(MIN('TrainingRecord'[TrainingDate])= _date,1)