Forum Discussion

danakajoel's avatar
danakajoel
Frequent Visitor
4 years ago
Solved

Percentage Difference reference previous Date and Attribute

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? ...
Need Help
  • danakajoel's avatar
    danakajoel
    4 years ago

    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's avatar
v-yingjl
Community Support
4 years ago

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.