Forum Discussion
Running Total using a base value from another table
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.
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
v-lili6-msft that solution worked perfectly much appreciated thank you.
6 Replies
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.
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])))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/38490Regards,
Lin
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
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
v-lili6-msft that solution worked perfectly much appreciated thank you.
