Running Total using a base value from another table

[chris_rowley]

So i have a table (1) currenlty with columns calculating runnings totals,

Booked_Running Total = calculate(
sum(All_Data[BOOKED]),
all(All_Data),
All_Data[DATE] <= EARLIER(All_Data[DATE]))

I then have another table (2) with 1 row that contains base values. Eseentially i would like to create 2 brand new calculated columns in table (1) that uses the base value in table (2) and performs a running total for each day adding the booked field each day to the base value

table (1)

DATA	All_Values	Category	Cat_Ref	DATE	BOOKED	CREATED	REBOOKED	Year	Week_Commencing	Booked_Running Total	Rebooked Running Total	Booked (ex.Rebooked)	MONTH
1393 | 2020-08-16 00:00:00 | TRIAGE_OUTCOME_RECORDED	1,393	TRIAGE_OUTCOME_RECORDED	44	16-Aug-20	0	0	0	2020	10-Aug-20	70,958,489	11,338,200	59,620,289	August
1 | 2020-08-16 00:00:00 | PATIENT_LOCK_REMOVED	1	PATIENT_LOCK_REMOVED	31	16-Aug-20	0	0	0	2020	10-Aug-20	70,958,489	11,338,200	59,620,289	August
46 | 2020-08-16 00:00:00 | ASSESSMENT_COMPLETED	46	ASSESSMENT_COMPLETED	20	16-Aug-20	0	0	0	2020	10-Aug-20	70,958,489	11,338,200	59,620,289	August
93 | 2020-08-16 00:00:00 | ASSESSMENT_RECORDED	93	ASSESSMENT_RECORDED	22	16-Aug-20	0	0	0	2020	10-Aug-20	70,958,489	11,338,200	59,620,289	August

Table (2)

Cumulative Bookings (inc. Rebookings)	Cumulative Bookings (ex.. Rebookings)	Date
71,359,625	62,643,542	14 June 2015

i have googled and googled and i cannot find any solutions that work. hope this makes sense.

[v-lili6-msft · Accepted solution]

hi  @chris_rowley 

Use this formula

Booked_Running Total = calculate(
sum(All_Data[Bookings]),
FILTER(all(All_Data),
All_Data[Date of Booking / Referral] <= EARLIER(All_Data[Date of Booking / Referral])))+SUM('Table(2)'[Overall_Total])

and why it doesn't answer your issue

please share your sample pbix file and your expected output.

Regards,

Lin

[chris_rowley]

hi all thank you for the assitance, unfortunatly the solutions provided didnt answer my issue which is partly my fault. Ill explain the issue a little better. in 2015 a software solution had been updated and as a result of this the data behind had changed it structure. however the figures pre the software change need to be kept hold off and one of the requirements from the data is to have a runing total per day.

here is the much simpler table (1)

Date of Booking / Referral	Bookings
15/06/15	20,167
16/06/15	24,560
17/06/15	13,298
18/06/15	45,362
19/06/15	39,242
20/06/15	4,211
21/06/15	1,747
22/06/15	44,703

Table (2) - this containts the previous running total and is needed for a base value

Overall_Total	Date
71,359,625	14-Jun-15

so essentially the calculation needs to be table (1)[bookings] + table (2)[Overall_Total] then provide a running total per day 

expected result table (1)

Date of Booking / Referral	Bookings	Running_Total_bookings_Per_day
15/06/15	20,167	71,379,792
16/06/15	24,560	71,404,352
17/06/15	13,298	71,417,650
18/06/15	45,362	71,463,012
19/06/15	39,242	71,502,254
20/06/15	4,211	71,506,465
21/06/15	1,747	71,508,212
22/06/15	44,703	71,552,915

hopefully this clears everything up

[v-lili6-msft · Accepted solution]

hi  @chris_rowley 

Use this formula

Booked_Running Total = calculate(
sum(All_Data[Bookings]),
FILTER(all(All_Data),
All_Data[Date of Booking / Referral] <= EARLIER(All_Data[Date of Booking / Referral])))+SUM('Table(2)'[Overall_Total])

and why it doesn't answer your issue

please share your sample pbix file and your expected output.

Regards,

Lin

[chris_rowley · Accepted solution]

@v-lili6-msft  that solution worked perfectly much appreciated thank you.

[v-lili6-msft]

hi  @chris_rowley 

If just create column as below in table(1)?

New column 1=SUM('Table(2)'[Cumulative Bookings (inc. Rebookings) ])+'Table(1)'[Booked_Running Total]

New column 2=SUM('Table(2)'[Cumulative Bookings (ex.. Rebookings) ])+'Table(1)'[Booked (ex.Rebooked)]

If not your case, please share your logic and expected output.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[amitchandak]

@chris_rowley , try this as column

Booked_Running Total = calculate(
sum(All_Data[BOOKED]),
filter(All_Data,
All_Data[DATE] <= EARLIER(All_Data[DATE])))

And this as a measure

Booked_Running Total = calculate(
sum(All_Data[BOOKED]),
filter(all(All_Data),
All_Data[DATE] <= max(All_Data[DATE])))

[parry2k]

@chris_rowley very hard to make sense out of your data table, can you paste the table with fewer columns and make sure these are not overlapping and merged together and also share what is your expected output.