Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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!

 

MonthSaleLast Year Sale
Jan-19100 
Feb-19200 
Mar-19300 
Apr-19400 
May-19500 
Jun-19600 
Jul-19700 
Aug-19800 
Sep-19900 
Oct-191000 
Nov-191100 
Dec-191200 
Jan-201300100
Feb-201400200
Mar-201500300
Apr-201600400
May-201700500
Jun-201800600
Jul-201900700
Aug-202000800
Sep-202100900
Oct-2022001000
Nov-2023001100
Dec-2024001200
1 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"

1.PNG

 

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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
Not applicable
Anonymous
Not applicable

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

 

 

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"

1.PNG

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.