Previous Year using M Query

[Omega]

Hi,

I have two columns, sales and date column. Using M Query, I know how to do it using DAX, I want to calculate the sales of previous year. 

Reason I need M query is because I need to do some pivoting and unpivoting which I can't using DAX. 

Thanks!

[v-yangliu-msft · Accepted solution]

Hi  @Omega ,

I created some data:

This is the step code of power query:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9JCgAhDETRu7gWseJ8FvH+1+gmBMph+yDh15xOosSAAOddccsbiMIgJIVOyAqNUBQyod5Pm0IldIVEGApCQFTB9hZPLK5a2J5OkLMWticT8vkVtqcS6hkH27P96PfJeMLiXWZztjSb8/9dHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, amount = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"amount", Int64.Type}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Current year", each #date(Date.Year(DateTime.LocalNow()) , Date.Month(DateTime.LocalNow())+1, 1)),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "last year", each #date(Date.Year(DateTime.LocalNow())-1 , Date.Month(DateTime.LocalNow()), 1)),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "IF", each if 

[date] >=[last year] and [date] < [Current year]

then

1

else

0),

    #"Grouped Rows" = Table.Group(#"Added Custom3", {"IF"}, {{"Count", each _, type table [date=nullable date, amount=nullable number, Current year=date, last year=date, IF=number]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Sum(Table.Column([Count],"amount"))),

    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"date", "amount", "Current year", "last year"}, {"date", "amount", "Current year", "last year"})

in

#"Expanded Count"

Result：

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Omega , Get a new column

Date.AddYears([Date], -1)

Then you group the data by this date and append it with other data