Forum Discussion

jak8282's avatar
jak8282
Icon for Helper III rankHelper III
3 years ago
Solved

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              Afr...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

jak8282's avatar
jak8282
Icon for Helper III rankHelper III
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • jak8282's avatar
      jak8282
      Icon for Helper III rankHelper III
      3 years ago

      Thanks Rena- that works great.