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

      • AjithTravvise's avatar
        AjithTravvise
        Helper II
        6 years ago

        Dear Maggie,

         

                Earlier one worked out to perfection, but when i added province column the country coulmn value repeated. That started causing issues. Below is the sample data for 2 days appreciated your help. Create a new colum where i will have the daily value either on province or country.

         

        Province/StateCountry/RegionLatLongDateValue
        AnhuiChina31.8257117.22641/22/20201
        BeijingChina40.1824116.41421/22/202014
        ChongqingChina30.0572107.8741/22/20206
        FujianChina26.0789117.98741/22/20201
        GuangdongChina23.3417113.42441/22/202026
        GuangxiChina23.8298108.78811/22/20202
        GuizhouChina26.8154106.87481/22/20201
        HainanChina19.1959109.74531/22/20204
        HebeiChina39.549116.13061/22/20201
        HenanChina33.882113.6141/22/20205
        HubeiChina30.9756112.27071/22/2020444
        HunanChina27.6104111.70881/22/20204
        JiangsuChina32.9711119.4551/22/20201
        JiangxiChina27.614115.72211/22/20202
        LiaoningChina41.2956122.60851/22/20202
        MacauChina22.1667113.551/22/20201
        NingxiaChina37.2692106.16551/22/20201
        ShandongChina36.3427118.14981/22/20202
        ShanghaiChina31.202121.44911/22/20209
        ShanxiChina37.5777112.29221/22/20201
        SichuanChina30.6171102.71031/22/20205
        TianjinChina39.3054117.3231/22/20204
        YunnanChina24.974101.4871/22/20201
        ZhejiangChina29.1832120.09341/22/202010
         Japan361381/22/20202
         Korea, South361281/22/20201
         Taiwan*23.71211/22/20201
         Thailand151011/22/20202
         US37.0902-95.71291/22/20201
        AnhuiChina31.8257117.22641/23/20209
        BeijingChina40.1824116.41421/23/202022
        ChongqingChina30.0572107.8741/23/20209
        FujianChina26.0789117.98741/23/20205
        GuangdongChina23.3417113.42441/23/202032
        GuangxiChina23.8298108.78811/23/20205
        GuizhouChina26.8154106.87481/23/20203
        HainanChina19.1959109.74531/23/20205
        HebeiChina39.549116.13061/23/20201
        HenanChina33.882113.6141/23/20205
        HubeiChina30.9756112.27071/23/2020444
        HunanChina27.6104111.70881/23/20209
        JiangsuChina32.9711119.4551/23/20205
        JiangxiChina27.614115.72211/23/20207
        LiaoningChina41.2956122.60851/23/20203
        MacauChina22.1667113.551/23/20202
        NingxiaChina37.2692106.16551/23/20201
        ShandongChina36.3427118.14981/23/20206
        ShanghaiChina31.202121.44911/23/202016
        ShanxiChina37.5777112.29221/23/20201
        SichuanChina30.6171102.71031/23/20208
        TianjinChina39.3054117.3231/23/20204
        YunnanChina24.974101.4871/23/20202
        ZhejiangChina29.1832120.09341/23/202027
         Japan361381/23/20202
         Korea, South361281/23/20201
         Taiwan*23.71211/23/20201
         Thailand151011/23/20203
         US37.0902-95.71291/23/20201

         

         

        Regards

        Ajith