Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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.
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |