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
GerardoRTobar
Helper I
Helper I

Managing the use of a multiplier that changes over time

Hello, community!

I'm looking for a way to manage the calculation of a metric that is affected by a multiplier. Imagine that I have a Sales Table and for each Sale a multiplier is considered to multiply the Sales Amount for every Salesman in order to calculate their Commision. I already have a Calendar Table, but I'm not finding a way to manage how to match the Sale Date to the multiplier that it should be affected by given that this multiplier is UNIQUE for each Salesman in a given (and not regular) period of time.

Imagine my Comission multipliers having these characteristics: 

 

SalesmanApplies to Sales fromApplies to Sales toComission
Salesman 11/Jan/202105/May/20210.85
Salesman 21/Jan/202107/Jun/20210.78
Salesman 31/Jan/202102/Jul/20210.65
Salesman 106/May/202131/Dic/20210.89
Salesman 208/Jun/202131/Dic/20210.87
Salesman 303/Jul/202131/Dic/20210.76
Salesman 11/Jan/202216/May/20220.85
Salesman 21/Jan/202221/Jun/20220.78
Salesman 31/Jan/202207/Jul/20220.65
Salesman 117/May/202231/Dic/20220.89
Salesman 222/Jun/202231/Dic/20220.87
Salesman 308/Jul/202231/Dic/20220.76

 

As you can see, the periods are not regular and I need to create a YEARLY visual that matches the sales for each day of the year with its corresponding commision and its corresponding Salesman. Is there any way to achieve this?

 

I would really appreciate your help with this regard and thanks in advance for doing so!

2 ACCEPTED SOLUTIONS
jbwtp
Memorable Member
Memorable Member

Hi @GerardoRTobar,

 

Would "unpivoting" dates this way help?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZAxD8IgEEb/imHuGe9IOdydmjg5Nh2I6YYsxsF/r1jwSmnCdmle+F7fOKqb8/Pz4cIBVacQBheA4tnD1b2X83S0vZq6FUslyzC8QmbZlqwuWfqyPrNm8278amRYI1zm+1/iXEtYWd7AXFtomS5hNrVGUv79aVKiVot4Ei5K1IpBKZzP7F4MZJlu1yCS7XYOK+N1jukD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Salesman = _t, #"Applies to Sales from" = _t, #"Applies to Sales to" = _t, Comission = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Salesman", type text}, {"Applies to Sales from", type date}, {"Applies to Sales to", type date}, {"Comission", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Applies to Sales from])..Number.From([Applies to Sales to])}),
    #"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Range",{{"Range", type date}})
in
    #"Changed Type1"

 

Or you need this be solved in DAX rather than PQ?

 

Cheers,

John

View solution in original post

Hi @GerardoRTobar,

 

You should not need the file. Just copy and paste the code above in the bank query. It should work.

Create a bank query:

jbwtp_0-1660173138875.png

 

Open it in the editor:

jbwtp_1-1660173204646.png

 

And then copy/paste the code in the editor and press Ok.

 

Kind regards,

John

View solution in original post

6 REPLIES 6
jbwtp
Memorable Member
Memorable Member

Hi @GerardoRTobar,

 

Would "unpivoting" dates this way help?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZAxD8IgEEb/imHuGe9IOdydmjg5Nh2I6YYsxsF/r1jwSmnCdmle+F7fOKqb8/Pz4cIBVacQBheA4tnD1b2X83S0vZq6FUslyzC8QmbZlqwuWfqyPrNm8278amRYI1zm+1/iXEtYWd7AXFtomS5hNrVGUv79aVKiVot4Ei5K1IpBKZzP7F4MZJlu1yCS7XYOK+N1jukD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Salesman = _t, #"Applies to Sales from" = _t, #"Applies to Sales to" = _t, Comission = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Salesman", type text}, {"Applies to Sales from", type date}, {"Applies to Sales to", type date}, {"Comission", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Applies to Sales from])..Number.From([Applies to Sales to])}),
    #"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Range",{{"Range", type date}})
in
    #"Changed Type1"

 

Or you need this be solved in DAX rather than PQ?

 

Cheers,

John

Regarding this original solution you suggested (which works fine!), is there a way to do it via DAX? I mean, the other one works fine but I think is not efficient because it produces one row per every combination, so I don't know if there's a DAX way to achieve the same result? 

Hi @GerardoRTobar,

 

Sorry, I can't figure it out how to do it in DAX. But to be perfectly honest, I am not much familiar with DAX.

Do you want to try to post your question in this hread: https://community.powerbi.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands ?

 

Kind regards,

John

I actually posted it here in PowerQuery thinking it would need some data prep stuff... But any solutions is welcomed!

Can you instead share a PowerBI file to open and test it?

Hi @GerardoRTobar,

 

You should not need the file. Just copy and paste the code above in the bank query. It should work.

Create a bank query:

jbwtp_0-1660173138875.png

 

Open it in the editor:

jbwtp_1-1660173204646.png

 

And then copy/paste the code in the editor and press Ok.

 

Kind regards,

John

I already tested your solutions and it works as intended!! Magnificent. Thanks a lot, buddy!

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.

Top Solution Authors
Top Kudoed Authors