Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jak8282
Helper III
Helper III

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.

1 ACCEPTED 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 )

yingyinr_0-1672987081872.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
jak8282
Helper III
Helper III

Hi Rena,

Apologies would want it like

 

DatekeyWC  Forecast    Region   Running Total
20221101   20221101   61Africa9
20221102   20221101   61Africa17
20221103   20221101   61Africa26
20221104   20221101   61Africa35
20221105   20221101   61Africa44
20221106   20221101   61Africa52
20221107   20221101   61Africa61
20221108   20221108   20Africa64
20221109   20221108   20Africa67
20221110   20221108   20Africa70
20221111   20221108   20Africa72
20221112   20221108   20Africa75
20221113   20221108   20Africa78
20221114   20221108   20Africa81

 

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 )

yingyinr_0-1672987081872.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Rena- that works great.

v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1672886583360.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

yingyinr_0-1672913515727.png

yingyinr_1-1672913526651.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.