Forum Discussion
Create a calculated running column based on week commencing total
Hi,
I have a table which I bring which looks like
Datekey WC Forecast Region
20221121 20221121 61 Africa
20221122 20221121 61 Africa
20221123 20221121 61 Africa
20221124 20221121 61 Africa
20221125 20221121 61 Africa
20221126 20221121 61 Africa
20221127 20221121 61 Africa
20221128 20221128 4 Africa
20221129 20221128 4 Africa
What would be the best way to go about creating a calculated column which creates a new column divides the week commencing total by 7 then and does a running total value plusing all the previous forecasts plus that days? So every day has a different total that is adding up based on previous days?
So for example
Datekey WC Forecast Region Running total
20221121 20221121 61 Africa 8.71
20221122 20221121 61 Africa 17.42
20221123 20221121 61 Africa 26.13
20221124 20221121 61 Africa
20221125 20221121 61 Africa
20221126 20221121 61 Africa
20221127 20221121 61 Africa
20221128 20221128 4 Africa
20221129 20221128 4 Africa
Thanks for any help.
Hi jak8282 ,
You can update the formula of calculated column [Running Total] as below, please find the details in the attachment.
Running Total = VAR _rtforecast = CALCULATE ( SUM ( 'Table'[Forecast] ), FILTER ( 'Table', 'Table'[Region] = EARLIER ( 'Table'[Region] ) && 'Table'[Datekey] <= EARLIER ( 'Table'[Datekey] ) ) ) RETURN DIVIDE ( _rtforecast, 7 )Best Regards
6 Replies
Hi jak8282 ,
You can create a calculated column as below to get it, please find the details in the attachment.
Running total = VAR _count = CALCULATE ( DISTINCTCOUNT ( 'Table'[Datekey] ), FILTER ( 'Table', 'Table'[WC] = EARLIER ( 'Table'[WC] ) ) ) VAR _rtforecast = CALCULATE ( SUM ( 'Table'[Forecast] ), FILTER ( 'Table', 'Table'[Region] = EARLIER ( 'Table'[Region] ) && 'Table'[WC] = EARLIER ( 'Table'[WC] ) && 'Table'[Datekey] <= EARLIER ( 'Table'[Datekey] ) ) ) RETURN DIVIDE ( _rtforecast, _count )If the above one can't help you get the expected result, please provide more raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi Rena,
It seems to be doing the divide by 7, but it does not seem to be doing the adding for the days?
As an example of your test file on the 20221128 date I would want that to be 63
See attached pbix
https://drive.google.com/file/d/1u1YQe9gHpuUBMKno85Lv3s0mvXG75XmB/view?usp=drivesdk
Kind Regards,
Chris
Hi jak8282 ,
Why it will be 63 when the date is on 20221128? Could you please provide the related calculation logic base on the current sample data? For example: WC: 20221201, what's the expected rolling total values for per date?
Best Regards
Hi Rena,
Apologies would want it like
Datekey WC Forecast Region Running Total 20221101 20221101 61 Africa 9 20221102 20221101 61 Africa 17 20221103 20221101 61 Africa 26 20221104 20221101 61 Africa 35 20221105 20221101 61 Africa 44 20221106 20221101 61 Africa 52 20221107 20221101 61 Africa 61 20221108 20221108 20 Africa 64 20221109 20221108 20 Africa 67 20221110 20221108 20 Africa 70 20221111 20221108 20 Africa 72 20221112 20221108 20 Africa 75 20221113 20221108 20 Africa 78 20221114 20221108 20 Africa 81 Thanks
Chris
Hi jak8282 ,
You can update the formula of calculated column [Running Total] as below, please find the details in the attachment.
Running Total = VAR _rtforecast = CALCULATE ( SUM ( 'Table'[Forecast] ), FILTER ( 'Table', 'Table'[Region] = EARLIER ( 'Table'[Region] ) && 'Table'[Datekey] <= EARLIER ( 'Table'[Datekey] ) ) ) RETURN DIVIDE ( _rtforecast, 7 )Best Regards
Thanks Rena- that works great.
