Calculate Measure1 as Measure2 + Measure3 Running Total

[niculeica]

Hi,

I'm trying to calculate Measure1 (Total Value) as Measure2 (Signed Contracts) + all backlog of Measure3 (Pending Contracts), everything displayed by date hierarchy (Year, Quarter, Month). So Measure3 would basicaly need to be transfromed into a Running Total of Pending Contracts.

Another way to put it would be Measure1 of March would be equal to signed Contracts of March plus March, Feb, Jan etc. (backwards) Pending Contracts.

Any suggestion? Thank you!

[niculeica]

Not sure how helpful it is, but Pending Contracts is calculated as follows:

1st Step, from the main Deals table:

_Renewal amount raw =

VAR CUR_STARTOFM =

MAX(_Timeline[_Start M])

VAR CUR_ENDOFM =

MAX(_Timeline[_End M])

RETURN

CALCULATE(SUM(Deal[amount_in_home_currency]),

FILTER(Deal,

Deal[_Pipeline]="Renewals - ARR"

&& Deal[dealstage]<>"3980406"&&Deal[dealstage]<>"4081009"&&Deal[dealstage]<>"4081010"&&Deal[dealstage]<>"3980407"

&& Deal[original_deal_contract_item_end_date]+1>=CUR_STARTOFM

&& Deal[original_deal_contract_item_end_date]+1<=CUR_ENDOFM))

2nd Step, in Timeline table where Deal[original_deal_contract_item_end_date]+1 is Summarized as main Column, to Sum months under quarters:

_Renewal amount = SUMX(SUMMARIZE(_Timeline,_Timeline[_M],"Renewal ARR",[_Renewal amount raw]),[Renewal ARR])

My Running Pending Contracts would have to be based on one of those two, I suppose. Currently the field in matrix the second formula, and I have the totals per each month, and not cummulatively.

[niculeica]

Hi, @speedramps,

Ok, so I have 2 measures already calculated. One is Signed contracts, which is the amount of "active contracts" (based of opportunity stage) and the other one is Pending contracts.

The matrix is displyed by Date hierarchy, with only Year, Quarter and Month (no Day). Signed contracts and Pending contracts are displayed correctly (data matches what I have in our CRM) per each month and quarter (SUM of Q = SUM of Months). I have pasted the previous screenshot thinking it's suggestive enough.

I think I might've added too much info, fair enough... basically what I'm trying to calculate is a Running Total of Pending Contracts (so I can simply sum it up with Signed Contracts; so my ultimate goal is sum of Signed contracts per date + sum Running Total Pending contracts per date). Below is the fashion I'd like to have Pending Contracts calculated (Amount is the individual sum per each time period, i.e. Month, but the Total interests me. It's what I'm trying to find the measure for. So it would be a Measure based Running Total (I think), and not Column based. Based on Amount, which I already hava the data for.

It's a little hard to comb through the data and attach it, as I'm talking 100k+ entries, sorry.

[speedramps]

Please can you just simple provide input data, desired output data and brief clear description.
I want to help but you are wasting your time and ours with vague waffle. 🙄

[niculeica]

@speedramps, at the very bottom is a data sample.

Needed:

- one measure = sum of Amount per year, quarter and month (date hierarchy) - in matrix;

- one measure = cummulative sum of Amount per year, quarter and month;

- desired output (cummulative sum of last month of q is equal to cummulative sum of q) right below. I hope it makes sense.

	Sum	Cummulative Sum
Jan	10	10
Feb	30	40
Mar	60	100
Total Q1	100	100
Apr	30	130
May	25	155
Jun	20	175
Total Q2	75	175

Record ID	Deal Name	Pipeline	Deal Stage	Amount in company currency	Close Date	Inactive Date
15860859474	Deal no. 142	Renewals - ARR	>365 Days	1994	2024-12-01 02:00	2024-12-01
15842177351	Deal no. 124	Renewals - ARR	<365 Days	1518	2024-11-01 02:00	2024-11-01
15678677277	Deal no. 40	Renewals - ARR	<365 Days	999	2024-10-01 03:00	2024-10-01
15611462181	Deal no. 269	Renewals - ARR	>365 Days	995	2026-09-05 03:00	2026-09-05
15557243932	Deal no. 282	Renewals - ARR	<365 Days	928	2024-11-01 02:00	2024-11-01
15541818683	Deal no. 83	Renewals - ARR	<365 Days	524	2024-11-01 02:00	2024-11-01
15516131724	Deal no. 195	Renewals - ARR	>365 Days	1529	2026-10-01 03:00	2026-10-01
15425010614	Deal no. 246	Renewals - ARR	<365 Days	1830	2024-11-01 02:00	2024-11-01
15421474859	Deal no. 154	Renewals - ARR	>365 Days	1824	2025-01-01 02:00	2025-01-01
15412867601	Deal no. 77	Renewals - ARR	<180 Days	1214	2024-03-10 02:00	2024-03-10
15279595559	Deal no. 193	Renewals - ARR	<90 Days	1027	2024-01-01 02:00	2024-01-01
15164440081	Deal no. 58	Renewals - ARR	<270 Days	1334	2024-06-01 03:00	2024-06-01
15164232203	Deal no. 119	Renewals - ARR	<90 Days	846	2024-01-01 02:00	2024-01-01
15118337967	Deal no. 51	Renewals - ARR	>365 Days	716	2026-03-01 02:00	2026-03-01
14994331225	Deal no. 118	Renewals - ARR	<180 Days	654	2024-03-01 02:00	2024-03-01
14992063493	Deal no. 67	Renewals - ARR	<90 Days	1418	2024-01-01 02:00	2024-01-01
14982132624	Deal no. 173	Renewals - ARR	<270 Days	506	2024-07-01 03:00	2024-07-01
14981603117	Deal no. 208	Renewals - ARR	<365 Days	1078	2024-09-01 03:00	2024-09-01
14980389804	Deal no. 288	Renewals - ARR	<30 Days	1350	2023-10-01 03:00	2023-10-01
14932638466	Deal no. 270	Renewals - ARR	<30 Days	516	2023-09-01 03:00	2023-09-01
14873726787	Deal no. 263	Renewals - ARR	<180 Days	1693	2024-03-01 02:00	2024-03-01
14824777142	Deal no. 106	Renewals - ARR	<180 Days	615	2024-04-01 03:00	2024-04-01

[speedramps · Accepted solution]

Click here to download the solution

Download PBIX 

How it works ...
Create a Calandar table

Create a measure

YTD cumulative = 
VAR myyear = SELECTEDVALUE('Calendar'[Year])
VAR myperiod = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Facts[Amount in company currency]),
    ALL('Calendar'),
    'Calendar'[Year] = myyear &&
    'Calendar'[Date] <= myperiod 
)

Dsiplay in matrix

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the thumbs up and the [accept as solution] button.  Thnak you.

[niculeica]

Thank you, @speedramps! I was able to put the solution to use.

[speedramps]

Is 2026 a typing mistake ?

[niculeica]

@speedramps , no everthing apart from amounts have been downloaded as is. I noticed the break in timeline, but it's fine. The idea is to redistribute the running total amount to the very next time period, with each month.

[speedramps]

See solution I provided

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember not to share private data ... we don't want you to get into trouble. 😧

Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time, care and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming