Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
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.
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
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
Country | Date | Incremental Total | Daily total |
Afghanistan | 1/20/2020 | 5 | 5 |
Albania | 1/20/2020 | 10 | 10 |
Algeria | 1/20/2020 | 2 | 2 |
Afghanistan | 1/21/2020 | 10 | 5 |
Albania | 1/21/2020 | 20 | 10 |
Algeria | 1/21/2020 | 2 | 0 |
Afghanistan | 1/22/2020 | 12 | 2 |
Albania | 1/22/2020 | 40 | 30 |
Algeria | 1/22/2020 | 4 | 4 |
Afghanistan | 1/23/2020 | 15 | 3 |
Albania | 1/23/2020 | 42 | 12 |
Algeria | 1/23/2020 | 17 | 13 |
Afghanistan | 1/24/2020 | 22 | 7 |
Albania | 1/24/2020 | 42 | 30 |
Algeria | 1/24/2020 | 23 | 10 |
Afghanistan | 1/25/2020 | 50 | 28 |
Albania | 1/25/2020 | 43 | 13 |
Algeria | 1/25/2020 | 26 | 16 |
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.
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.
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/State | Country/Region | Lat | Long | Date | Value |
Anhui | China | 31.8257 | 117.2264 | 1/22/2020 | 1 |
Beijing | China | 40.1824 | 116.4142 | 1/22/2020 | 14 |
Chongqing | China | 30.0572 | 107.874 | 1/22/2020 | 6 |
Fujian | China | 26.0789 | 117.9874 | 1/22/2020 | 1 |
Guangdong | China | 23.3417 | 113.4244 | 1/22/2020 | 26 |
Guangxi | China | 23.8298 | 108.7881 | 1/22/2020 | 2 |
Guizhou | China | 26.8154 | 106.8748 | 1/22/2020 | 1 |
Hainan | China | 19.1959 | 109.7453 | 1/22/2020 | 4 |
Hebei | China | 39.549 | 116.1306 | 1/22/2020 | 1 |
Henan | China | 33.882 | 113.614 | 1/22/2020 | 5 |
Hubei | China | 30.9756 | 112.2707 | 1/22/2020 | 444 |
Hunan | China | 27.6104 | 111.7088 | 1/22/2020 | 4 |
Jiangsu | China | 32.9711 | 119.455 | 1/22/2020 | 1 |
Jiangxi | China | 27.614 | 115.7221 | 1/22/2020 | 2 |
Liaoning | China | 41.2956 | 122.6085 | 1/22/2020 | 2 |
Macau | China | 22.1667 | 113.55 | 1/22/2020 | 1 |
Ningxia | China | 37.2692 | 106.1655 | 1/22/2020 | 1 |
Shandong | China | 36.3427 | 118.1498 | 1/22/2020 | 2 |
Shanghai | China | 31.202 | 121.4491 | 1/22/2020 | 9 |
Shanxi | China | 37.5777 | 112.2922 | 1/22/2020 | 1 |
Sichuan | China | 30.6171 | 102.7103 | 1/22/2020 | 5 |
Tianjin | China | 39.3054 | 117.323 | 1/22/2020 | 4 |
Yunnan | China | 24.974 | 101.487 | 1/22/2020 | 1 |
Zhejiang | China | 29.1832 | 120.0934 | 1/22/2020 | 10 |
Japan | 36 | 138 | 1/22/2020 | 2 | |
Korea, South | 36 | 128 | 1/22/2020 | 1 | |
Taiwan* | 23.7 | 121 | 1/22/2020 | 1 | |
Thailand | 15 | 101 | 1/22/2020 | 2 | |
US | 37.0902 | -95.7129 | 1/22/2020 | 1 | |
Anhui | China | 31.8257 | 117.2264 | 1/23/2020 | 9 |
Beijing | China | 40.1824 | 116.4142 | 1/23/2020 | 22 |
Chongqing | China | 30.0572 | 107.874 | 1/23/2020 | 9 |
Fujian | China | 26.0789 | 117.9874 | 1/23/2020 | 5 |
Guangdong | China | 23.3417 | 113.4244 | 1/23/2020 | 32 |
Guangxi | China | 23.8298 | 108.7881 | 1/23/2020 | 5 |
Guizhou | China | 26.8154 | 106.8748 | 1/23/2020 | 3 |
Hainan | China | 19.1959 | 109.7453 | 1/23/2020 | 5 |
Hebei | China | 39.549 | 116.1306 | 1/23/2020 | 1 |
Henan | China | 33.882 | 113.614 | 1/23/2020 | 5 |
Hubei | China | 30.9756 | 112.2707 | 1/23/2020 | 444 |
Hunan | China | 27.6104 | 111.7088 | 1/23/2020 | 9 |
Jiangsu | China | 32.9711 | 119.455 | 1/23/2020 | 5 |
Jiangxi | China | 27.614 | 115.7221 | 1/23/2020 | 7 |
Liaoning | China | 41.2956 | 122.6085 | 1/23/2020 | 3 |
Macau | China | 22.1667 | 113.55 | 1/23/2020 | 2 |
Ningxia | China | 37.2692 | 106.1655 | 1/23/2020 | 1 |
Shandong | China | 36.3427 | 118.1498 | 1/23/2020 | 6 |
Shanghai | China | 31.202 | 121.4491 | 1/23/2020 | 16 |
Shanxi | China | 37.5777 | 112.2922 | 1/23/2020 | 1 |
Sichuan | China | 30.6171 | 102.7103 | 1/23/2020 | 8 |
Tianjin | China | 39.3054 | 117.323 | 1/23/2020 | 4 |
Yunnan | China | 24.974 | 101.487 | 1/23/2020 | 2 |
Zhejiang | China | 29.1832 | 120.0934 | 1/23/2020 | 27 |
Japan | 36 | 138 | 1/23/2020 | 2 | |
Korea, South | 36 | 128 | 1/23/2020 | 1 | |
Taiwan* | 23.7 | 121 | 1/23/2020 | 1 | |
Thailand | 15 | 101 | 1/23/2020 | 3 | |
US | 37.0902 | -95.7129 | 1/23/2020 | 1 |
Regards
Ajith
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.
Country | Date | Running Value | Daily Figure(need to be calculated) |
Japan | 1/20/20 | 1 | 1 |
India | 1/20/20 | 5 | 5 |
Japan | 1/21/20 | 3 | 2 |
India | 1/21/20 | 5 | 0 |
Japan | 1/22/20 | 8 | 5 |
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"
Hi Mariusz,
Thanks for the below. This is perfect, but how did you do this, can you share the measure.
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.
Thank you so much....