Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
@Omega , Get a new column
Date.AddYears([Date], -1)
Then you group the data by this date and append it with other data
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |