Column IF Statement with two variables help

[Vtomsons]

Hi,

I am trying to calculate the value of all course registrations that occured after a special promotion price course to determine if a promotional campaign yielded adequate revenue and am coming across a summation issue. Hope someone can suggest a way to get around it.

I would like to count all the registrations that occur after a promotion course was completed so decided to use the course "registration start date" as the key indicator to count if it occurs after the promo course. Issue is I have more then one promo course per student so the equation below works but it doubles or triples the course count when the criteria is met so the sum is off (which I then use to calculate a dollar amount).

Here's the new column I created: 

Promo Next Course Amount =
IF([Registration Type]="Promo" && [Registration Status]="Completed",
var s = [Student ID]
var rt = [Registration Type]
var cn = [Offerings Name]
var sd = [Registration start date]
var r = calculate(COUNTROWS('ONLINE Reg''n'),ALL('ONLINE Reg''n'),'ONLINE Reg''n'[Student ID]=s,'ONLINE Reg''n'[Registration Type]<>"Promo",'ONLINE Reg''n'[Registration start date]>sd)
return r)

This equation works perfect when there is only one promo course. But when there are multiple promo courses, my next equation summarizes all from this column and makes the final number alot higher then it actually is. 

Promo Revenue = 'ONLINE Reg''n'[Course Cost]*'ONLINE Reg''n'[Promo Next Course Amount]

I was hoping I could only count the most recent promo course (based on registration start date) to avoid this summary issue.

Here's an example that calculates 4X more then I want it to. 

Contact	Courses Name	Registration Status	Registration start date	Registration Type	Course cost	Promo Next course Amount	Promo Revenue
Liban Farah	COHS 3110	Completed	10/01/2022		595
Liban Farah	COHS 3120	Completed	14/02/2022	Promo	595	6	3570
Liban Farah	COHS 3130	Completed	07/04/2022	Promo	595	6	3570
Liban Farah	COHS 3210	Completed	14/04/2022	Promo	595	6	3570
Liban Farah	COHS 3220	Completed	19/04/2022	Promo	595	6	3570
Liban Farah	COHS 3230	Completed	21/04/2022		595
Liban Farah	COHS 3310	Completed	28/04/2022		595
Liban Farah	COHS 3320	Completed	28/04/2022		595
Liban Farah	COHS 3330	Completed	28/04/2022		595
Liban Farah	COHS 3340	Completed	28/04/2022		595
Liban Farah	COHS 3410	Completed	28/04/2022		595
Total						24	14280

Yes the student registered for 6 courses with a registration start date greater then the new start date but this result below is what I hope someone could get me towards. Only return for the most recent course with a Promo rwegistration type. Thanks for any help

Contact	Courses Name	Registration Status	Registration start date	Registration Type	Course cost	Promo Next course Amount	Promo Revenue
Liban Farah	COHS 3110	Completed	10/01/2022		595
Liban Farah	COHS 3120	Completed	14/02/2022	Promo	595
Liban Farah	COHS 3130	Completed	07/04/2022	Promo	595
Liban Farah	COHS 3210	Completed	14/04/2022	Promo	595
Liban Farah	COHS 3220	Completed	19/04/2022	Promo	595	6	3570
Liban Farah	COHS 3230	Completed	21/04/2022		595
Liban Farah	COHS 3310	Completed	28/04/2022		595
Liban Farah	COHS 3320	Completed	28/04/2022		595
Liban Farah	COHS 3330	Completed	28/04/2022		595
Liban Farah	COHS 3340	Completed	28/04/2022		595
Liban Farah	COHS 3410	Completed	28/04/2022		595
Total						6	3570

[amitchandak · Accepted solution]

@Vtomsons , refer if my blog to get latest value can help

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Vtomsons]

Thank you Amit. I think I almost have it using the two measures on your blog. One scenario is not working though. If the latest registration start date is the same for multiple promo courses, the sum is calculating all three rather then just the one. The measure works but my data for some have the same registration start date for the latest promo course, ugh. Any ideas on how to account for this in one of these measures from your blog? Thanks again!

Last Promo Date Qty =
var _max = MAXX(FILTER(ALLSELECTED('ONLINE Reg''n'), 'ONLINE Reg''n'[Student ID] = MAX('ONLINE Reg''n'[Student ID])), 'ONLINE Reg''n'[Registration start date])
return CALCULATE(SUM('ONLINE Reg''n'[Promo Revenue]),FILTER('ONLINE Reg''n','ONLINE Reg''n'[Student ID]=MAX('ONLINE Reg''n'[Student ID]) && ('ONLINE Reg''n'[Registration start date] = _max)))

Promo Revenue SUM = SUMX(VALUES('ONLINE Reg''n'[Student ID]), 'ONLINE Reg''n'[Last Promo Date Qty])