DAX Help! Return date that cumulative total surpassed a column value

[CSmith_HW]

Hi guys,

I have a campaign table that look like the below:

Campaign ID	Target Price
1	£50,000
2	£70,000
3	£120,000
4	£35,000

and a table of deals that looks like:

Deal ID	Campaign ID	Price	Date
1	1	£5,000	08/02/2021
2	2	£7,000	24/12/2020
3	2	£11,000	12/01/2021
4	3	£13,500	30/10/2020
5	1	£3,500	01/02/2021

The 2 tables are joined in a 1-to-many via campaign ID. Now, what I would like to do, preferably in a calculated column on the campaign table is return the date at which the cumulative sum of "Price" surpassed the Target Price.

So the logic would look something like this:

1. Use the date on the deal table to create a cumulative running total of all deals for each campaign.

2. Monitor when the cumulative running total surpasses the target price

3. Return the date that final deal happened to take us over the target price

Any help would be great,

Thanks

[rfigtree · Accepted solution]

hi i have a problem understanding context transition but i think this works.

probably a much neater way of doing it.

had to fiddle your data as none of the targets met as you put them.

[CSmith_HW]

Yep I used dummy data in my example - it works though thanks!