Forum Discussion
Last year sales Column in Query Editor
Hi All,
How to calculate last year sales for same time period in Query editor?I need to use Unpivot option hence i need to claculate those in Edit query.
Thanks in Advance!
| Month | Sale | Last Year Sale |
| Jan-19 | 100 | |
| Feb-19 | 200 | |
| Mar-19 | 300 | |
| Apr-19 | 400 | |
| May-19 | 500 | |
| Jun-19 | 600 | |
| Jul-19 | 700 | |
| Aug-19 | 800 | |
| Sep-19 | 900 | |
| Oct-19 | 1000 | |
| Nov-19 | 1100 | |
| Dec-19 | 1200 | |
| Jan-20 | 1300 | 100 |
| Feb-20 | 1400 | 200 |
| Mar-20 | 1500 | 300 |
| Apr-20 | 1600 | 400 |
| May-20 | 1700 | 500 |
| Jun-20 | 1800 | 600 |
| Jul-20 | 1900 | 700 |
| Aug-20 | 2000 | 800 |
| Sep-20 | 2100 | 900 |
| Oct-20 | 2200 | 1000 |
| Nov-20 | 2300 | 1100 |
| Dec-20 | 2400 | 1200 |
Hi Anonymous ,
It need to merge tables. Please try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/JDYBADEPRXnJGIsnstYzovw0EDgq+Pvngv7fYaaerLTnEVOU6tniSB5WkElSTalBLakE9qQeNpBE0k2bQSlpBpvQ18B/wFdgvwb4GDF0fK5QKq9QKaxQL61QLG5QLm9QLWxT8misVw4yTgc7JwMLJwDflugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Sale = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Sale", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddYears([Month],-1)), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Added Custom", {"Month"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Sale"}, {"Sale.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"Custom"}) in #"Removed Columns"
5 Replies
Hi Anonymous ,
It need to merge tables. Please try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/JDYBADEPRXnJGIsnstYzovw0EDgq+Pvngv7fYaaerLTnEVOU6tniSB5WkElSTalBLakE9qQeNpBE0k2bQSlpBpvQ18B/wFdgvwb4GDF0fK5QKq9QKaxQL61QLG5QLm9QLWxT8misVw4yTgc7JwMLJwDflugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Sale = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Sale", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddYears([Month],-1)), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Added Custom", {"Month"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Sale"}, {"Sale.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"Custom"}) in #"Removed Columns"
Anonymous , with time intelligence you can try
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
In case ytd is needed
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
