Percentage Difference reference previous Date and Attribute

[danakajoel]

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%

[danakajoel · Accepted 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

[v-yingjl]

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.

[Jakinta]

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"