Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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"

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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.