Forum Discussion
DAX Help! Return date that cumulative total surpassed a column value
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
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.
