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

CSmith_HW — Fri, 12 Feb 2021 10:15:42 GMT

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:

Use the date on the deal table to create a cumulative running total of all deals for each campaign.
Monitor when the cumulative running total surpasses the target price
Return the date that final deal happened to take us over the target price

Any help would be great,

Thanks

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

rfigtree — Fri, 12 Feb 2021 13:09:44 GMT

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.

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

CSmith_HW — Fri, 12 Feb 2021 14:51:42 GMT

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

topic Re: DAX Help! Return date that cumulative total surpassed a column value in DAX Commands and Tips

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

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

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