Last year sales Column in Query Editor

[Anonymous]

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

[amitchandak]

@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.

[Anonymous]

Does this work?

https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question 

[Anonymous]

It's need to be crated in Query editor with M lang., So that I can use Unpivot Propery for few columns.

[v-xuding-msft · Accepted solution]

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"

[PKP277]

Did you get a solution?