Forum Discussion

AjithTravvise's avatar
AjithTravvise
Helper II
6 years ago
Solved

Running total to date

Hello,         I have the below data where the value is running total, need help in creating a a column where it will populate the date wise    
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi AjithTravvise 

    If the date increases daily, you could create calculated columns below:

    earlier =
CALCULATE (
    SUM ( 'Table'[Running Value] ),
    FILTER (
        'Table',
        'Table'[Country]
            = EARLIER ( 'Table'[Country] )
            && 'Table'[Date]
                = EARLIER ( 'Table'[Date] ) - 1
    )
)

daily total = [Running Value]-[earlier]

    As tested, if i understand you correctly, your second visual has some wrong data due to teh calculation rule.

     

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

  • AjithTravvise's avatar
    AjithTravvise
    6 years ago

    Thank you so much, it work out to perfection. 

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi AjithTravvise 

     

    This solution was created using Power Query as you have posted your question in PQ section, the script dose Include the M code in Added Column step of the script.

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

AjithTravvise's avatar
AjithTravvise
Helper II
6 years ago

Hi Greg,

 

    I tried all could, but stiil nothing worked, my request is very straight forward, i need to breakdown the incremental value on date wise in Daily Total Column. Appreciated your help

 

CountryDateIncremental TotalDaily total
Afghanistan1/20/202055
Albania1/20/20201010
Algeria1/20/202022
Afghanistan1/21/2020105
Albania1/21/20202010
Algeria1/21/202020
Afghanistan1/22/2020122
Albania1/22/20204030
Algeria1/22/202044
Afghanistan1/23/2020153
Albania1/23/20204212
Algeria1/23/20201713
Afghanistan1/24/2020227
Albania1/24/20204230
Algeria1/24/20202310
Afghanistan1/25/20205028
Albania1/25/20204313
Algeria1/25/20202616
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi AjithTravvise 

    If the date increases daily, you could create calculated columns below:

    earlier =
CALCULATE (
    SUM ( 'Table'[Running Value] ),
    FILTER (
        'Table',
        'Table'[Country]
            = EARLIER ( 'Table'[Country] )
            && 'Table'[Date]
                = EARLIER ( 'Table'[Date] ) - 1
    )
)

daily total = [Running Value]-[earlier]

    As tested, if i understand you correctly, your second visual has some wrong data due to teh calculation rule.

     

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

    • AjithTravvise's avatar
      AjithTravvise
      Helper II
      6 years ago

      Thank you so much, it work out to perfection. 

    • AjithTravvise's avatar
      AjithTravvise
      Helper II
      6 years ago

      Hi Juanli,

       

           Thanks for your earliet help, but when i added one more column which is province, the daily total shows negative value. It would be appreciated if you could help.

       

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi AjithTravvise 

        Does the [incremental total] sum values in country level or in province level?

        is the column ok?

        earlier =
CALCULATE (
    SUM ( 'Table'[Running Value] ),
    FILTER (
        'Table',
        'Table'[Country]
            = EARLIER ( 'Table'[Country] )
        &&'Table'[Province]=EARLIER('Table'[Country])
            && 'Table'[Date]
                = EARLIER ( 'Table'[Date] ) - 1
    )
)

         

        If not, please share a simple sample for me to test.

         

        Best Regards

        Maggie