Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community.
I have a data set where I am trying to calculate the percentage change between current date and the previous date for different types of stocks in M Query. Is this possible please?
Here's an example where I've added a Percent column in Excel where the first percentage change -0.73% is 2693.637/2673.853 - 1
i.e. at row level, it looks at the current stock, Equities, finds current value and the previous value the day before and calculates the % difference
Appreciate I could approach this in DAX but considering M in the first instance
TIA
Date | Attribute | Value | Percent |
31/12/2020 | Equities | 2693.637 | |
31/12/2020 | Government Bonds | 251.95 | |
31/12/2020 | Credit | 303.92 | |
31/12/2020 | Alternatives | 16543.53 | |
01/01/2021 | Equities | 2693.637 | 0.00% |
01/01/2021 | Government Bonds | 251.95 | 0.00% |
01/01/2021 | Credit | 303.9205 | 0.00% |
01/01/2021 | Alternatives | 16543.53 | 0.00% |
02/01/2021 | Equities | 2693.637 | 0.00% |
02/01/2021 | Government Bonds | 251.95 | 0.00% |
02/01/2021 | Credit | 303.9205 | 0.00% |
02/01/2021 | Alternatives | 16543.53 | 0.00% |
03/01/2021 | Equities | 2693.637 | 0.00% |
03/01/2021 | Government Bonds | 251.95 | 0.00% |
03/01/2021 | Credit | 303.9205 | 0.00% |
03/01/2021 | Alternatives | 16543.53 | 0.00% |
04/01/2021 | Equities | 2673.853 | -0.73% |
04/01/2021 | Government Bonds | 252.38 | 0.17% |
04/01/2021 | Credit | 303.9729 | 0.02% |
04/01/2021 | Alternatives | 16543.53 | 0.00% |
05/01/2021 | Equities | 2688.223 | 0.54% |
05/01/2021 | Government Bonds | 252.44 | 0.02% |
05/01/2021 | Credit | 303.3026 | 0.22% |
05/01/2021 | Alternatives | 16543.53 | 0.00% |
06/01/2021 | Equities | 2703.601 | 0.57% |
06/01/2021 | Government Bonds | 251.42 | -0.40% |
06/01/2021 | Credit | 301.948 | -0.45% |
06/01/2021 | Alternatives | 16543.53 | 0.00% |
07/01/2021 | Equities | 2738.771 | 1.30% |
07/01/2021 | Government Bonds | 250.58 | -0.33% |
07/01/2021 | Credit | 301.5959 | -0.12% |
07/01/2021 | Alternatives | 16543.53 | 0.00% |
08/01/2021 | Equities | 2758.144 | 0.71% |
08/01/2021 | Government Bonds | 250.62 | 0.02% |
08/01/2021 | Credit | 301.5572 | -0.01% |
08/01/2021 | Alternatives | 16543.53 | 0.00% |
Solved! Go to Solution.
thank you for the replies and help. I have this now working in DAX but will unpick the Power Query method as I believe that would ultimately be more elegant. Thanks again
thank you for the replies and help. I have this now working in DAX but will unpick the Power Query method as I believe that would ultimately be more elegant. Thanks again
Hi @danakajoel ,
Power Query method:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdHBagMhEIDhVyl7DrM64+h4bEPoQ4QcCtnDQruhic3zVwILdcxW8SL4M34wx+OABs1ocSQ77IbD98+c5umWr+gjgacwnHYqer/cp+vyNS3p5e2ynB8xW4hcp/vrdJ5TvpAhiFgHr58pz/pI8/3xqfXsCJjW0I75/O9ak6ZqDZXJVEGHCdsm7DVhy4R9JmqbqNdELRP1mZw2BQKpk6cmBBIdlqaAUQcdJtYmEUCskg2TczosTGTQ66DD5JUp5FHeWJ1s7M6hDv+Y8mqd6PcOUtAkEgjB6uQpyQCLDgsSR4466DCJNrGALTci2yaPOixNHKpg03T6BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Attribute = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Attribute", type text}, {"Value", type number}}),
#"Grouped Rows" =
Table.Group(
#"Changed Type", {"Attribute"},
{
{
"Data", each
let tab = Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in
Table.AddColumn(
tab, "New",
(x)=> try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Value]
otherwise null
)
}
}
),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Attribute", "Value", "New"}, {"Data.Date", "Data.Attribute", "Data.Value", "Data.New"}),
#"Added Custom" = Table.AddColumn(#"Expanded Data", "Percent", each if [Data.New] = null then null else
[Data.Value] / [Data.New] - 1),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Data.New"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Data.Date", "Date"}, {"Data.Attribute", "Attribute"}, {"Data.Value", "Value"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Attribute", type text}, {"Value", type number}, {"Percent", Percentage.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}})
in
#"Sorted Rows"
Dax method:
Dax percent =
VAR _d = [Date]
VAR _a = [Attribute]
VAR pre =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Attribute] = _a
&& 'Table'[Date]
= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Attribute] = _a && 'Table'[Date] < _d )
)
)
)
RETURN
IF ( ISBLANK ( pre ), BLANK (), [Value] / pre - 1 )
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This can help...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdHBasMwDIDhVxk5F8WWLFs+bmPsIUoPg+YQ2FLWZn3+mcBgrmzH4IMOP/IHOh4HsqPFEQ2a4TC8ff/M6zzd0og+EngKw+nwEL1f7tN1+ZqW9enlspy3mC1E1unrdTrPaxrIEETUwfPnmnZ9rPN9+9R6dgRMW2jsmF4KbUOWRW1Zlj7ITCFp2rDHhv023Ldhr416bNRvo30b9dpczRYIpBQVbQgkOs1tAaNOmjau2UQAsRBVbM7pNLORQa+Tps1XbCGt88bqqHJThzr9Z0tHd6KLJi3UaCQQgtVRkWaARacZjSNHnTRtUrOxgP27k+zbPOo0t3EoJFXb6Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Attribute = _t, Value = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}),
DateLocale = Table.TransformColumnTypes(ChangedType, {{"Date", type date}}, "hr-HR"),
Custom1 = Table.AddIndexColumn(DateLocale, "Index",0,1),
Grouped = Table.Group(Custom1, {"Attribute"}, {{"Gr", each let t=Table.AddIndexColumn( Table.Sort( _ , {"Date", Order.Ascending}), "Index2",-1,1) in Table.AddColumn( t, "Percent", each try ([Value] - t[Value]{[Index2]})/[Value] otherwise 0 ) , type table }}),
Removed = Table.RemoveColumns(Grouped,{"Attribute"}),
Expanded = Table.ExpandTableColumn(Removed, "Gr", {"Date", "Attribute", "Value", "Index", "Index2", "Percent"}, {"Date", "Attribute", "Value", "Index", "Index2", "Percent"}),
#"Changed Type" = Table.TransformColumnTypes(Expanded,{{"Percent", Percentage.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Index2"})
in
#"Removed Columns"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |