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
AjithTravvise
Helper II
Helper II

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  

 

Data.PNG

4 ACCEPTED SOLUTIONS

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]

Capture5.JPGCapture6.JPG

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.

View solution in original post

Thank you so much, it work out to perfection. 

View solution in original post

Hi @AjithTravvise 

check my pbix

Capture16.JPG

 

Best Regards

Maggie

View solution in original post

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

 

View solution in original post

14 REPLIES 14
Greg_Deckler
Super User
Super User

I do not understand. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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]

Capture5.JPGCapture6.JPG

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.

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.

 

Data.PNG

 

 

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

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    

Hi @AjithTravvise 

check my pbix

Capture16.JPG

 

Best Regards

Maggie

Dear Maggie,

 

   Thank you so much for the help. It worked as desired. I am just a begginer and I am hoping to get help as and when i am stuck. 

 

Cheers 

Ajith

Thank you so much, it work out to perfection. 

Greg,

 

    I have the below columes- Country/ Date/ Running value.... I wanted create a new column Daily figure, which will be the total value of that day. 

 

 

CountryDateRunning ValueDaily Figure(need to be calculated)
Japan1/20/2011
India1/20/2055
Japan1/21/2032
India1/21/2050
Japan1/22/2085

Hi @AjithTravvise 

 

Please see the below script or attached file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kosSMxT0lEyMjAy0DfUNzIAsg2VYnWilTzzUjIT0WRMwTLoegyBbGOsegxx6jECsi2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, Date = _t, #"Running Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Date", type date}, {"Running Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"tbl", each _, type table [Country=text, Date=date, Running Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each   
        let 
            tbl = Table.Sort( [tbl], "Date" ),
            count = Table.RowCount( tbl ),
            lst = { 0..count -1 },
            transform = List.Transform( lst, 
                (i) => [ Value = try tbl{ i }[Running Value] - tbl{ i-1 }[Running Value] otherwise tbl{ i }[Running Value] ] & tbl{ i } 
            )
        in 
            transform
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"tbl"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Value", "Date", "Running Value"}, {"Value", "Date", "Running Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Value", Int64.Type}, {"Date", type date}, {"Running Value", Int64.Type}})
in
    #"Changed Type1"

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

Hi Mariusz,

 

     Thanks for the below. This is perfect, but how did you do this, can you share the measure.

 

 

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

 

Thank you so much....

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.

Top Solution Authors
Top Kudoed Authors