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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
williamadams12
Resolver I
Resolver I

Calculated Measure Using Multiple Dates and Multiple Percentages

I have a tricky issue I'm currently working through in PowerBI.

 

I have a data model where I have 12 different dates (in columns) with 12 different percentages (also in columns), but often there is only one necessary date and no percent due, usually a weight that's considered as well, but sometimes there are many dates and many percents I need to use to calculate % fees per month based on when these accrual amounts are due. See the model below: 

 

1st Invoice Date1st Invoice % Due2nd Invoice Date2nd Invoice % Due3rd Invoice Date3rd Invoice % DueOverall Weight %Total Net Fee
1/1/20206%2/1/20206%3/1/202088%80%100,000.00

 

The logic here is as follows: for the January weighed amount you'd calculate (TotalNetFee*1st Invoice % Due)*Overall Weight % (if necessary), so you'd get your weighted net fee for the % amount of the first invoice amount due, so in this case, that'd be $4800 for January 2020. And 4800 for February 2020. 

 

Previously, I had created a measure which only needed to utilize the 1st invoice date, the overall weight and the total net fee, which was pretty straightforward, but now i need to incorporate additional logic for multiple dates and the % amounts due per date. 

 

Has anyone developed a measure with a dozen different dates and multiple aggregations? I need this to be properly reflected in a column chart for a data model visualization I'm working on. 

 

Any help or ideas for models, meaures or pwoer query edits that anyone has would be greatly appreciated! 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

I'm not sure I fully understand your goal, but you are going to have to normalize this table for this to work at all in DAX. The below M code in Power Query will transform your original table to the following:

2020-06-08 09_57_39-Untitled - Power Query Editor.png

Does that help? Now you just need to filter on the dates and run some cumulative totals. If this is going down the right track, post back if you need additional help with the DAX side, or if the above doesn't look right.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEyUwUSRmh8YwTfwgIkYGEAIg0NDHQMDAz0DAyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1st Invoice Date" = _t, #"1st Invoice % Due" = _t, #"2nd Invoice Date" = _t, #"2nd Invoice % Due" = _t, #"3rd Invoice Date" = _t, #"3rd Invoice % Due" = _t, #"Overall Weight %" = _t, #"Total Net Fee" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1st Invoice Date", type date}, {"1st Invoice % Due", Percentage.Type}, {"2nd Invoice Date", type date}, {"2nd Invoice % Due", Percentage.Type}, {"3rd Invoice Date", type date}, {"3rd Invoice % Due", Percentage.Type}, {"Overall Weight %", Percentage.Type}, {"Total Net Fee", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Date", each if Value.Is([Column2], type date) = true then [Column2] else null, type date),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Filled Down", {{"Column1", each Text.AfterDelimiter(_, " "), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text After Delimiter", each ([Column1] = "Invoice % Due" or [Column1] = "Net Fee")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Column1]), "Column1", "Column2"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Invoice % Due", Percentage.Type}, {"Net Fee", Currency.Type}})
in
    #"Changed Type2"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

9 REPLIES 9
edhans
Super User
Super User

I'm not sure I fully understand your goal, but you are going to have to normalize this table for this to work at all in DAX. The below M code in Power Query will transform your original table to the following:

2020-06-08 09_57_39-Untitled - Power Query Editor.png

Does that help? Now you just need to filter on the dates and run some cumulative totals. If this is going down the right track, post back if you need additional help with the DAX side, or if the above doesn't look right.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEyUwUSRmh8YwTfwgIkYGEAIg0NDHQMDAz0DAyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1st Invoice Date" = _t, #"1st Invoice % Due" = _t, #"2nd Invoice Date" = _t, #"2nd Invoice % Due" = _t, #"3rd Invoice Date" = _t, #"3rd Invoice % Due" = _t, #"Overall Weight %" = _t, #"Total Net Fee" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1st Invoice Date", type date}, {"1st Invoice % Due", Percentage.Type}, {"2nd Invoice Date", type date}, {"2nd Invoice % Due", Percentage.Type}, {"3rd Invoice Date", type date}, {"3rd Invoice % Due", Percentage.Type}, {"Overall Weight %", Percentage.Type}, {"Total Net Fee", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Date", each if Value.Is([Column2], type date) = true then [Column2] else null, type date),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Filled Down", {{"Column1", each Text.AfterDelimiter(_, " "), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text After Delimiter", each ([Column1] = "Invoice % Due" or [Column1] = "Net Fee")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Column1]), "Column1", "Column2"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Invoice % Due", Percentage.Type}, {"Net Fee", Currency.Type}})
in
    #"Changed Type2"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans Correct, my initial thought was the need to unpivot all of these date columns columns so they're rows and can then be used with functions with M Power Query or DAX. Thanks for the M Code. I just wasn't sure if there was anything worth trying in DAX with the initial iteration of my data model. I'll post some updates once I've made some progress. 

@edhans - i'm really struggling with the double unpivot because i can't seem to align the multiple invoice dates with their invoice %s. any ideas?misaligned dates.PNG

 

Not sure where those other fields come into play, but if you look at my M code, I am not unpivoting. I am transposing and pivoting.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans wanted to thank you for your help on this, even if I'm a little late responding. I ended up doing the entire transformation in my Alteryx macros/workflows and just wrote the output to a database for PowerBI to reference as the data source. 

 

This was a little tricky, but ended up having to do a series of multiple joins for the accrual date and accrual number and accrual % to all align perfectly and calcualte accurately. 

 

PowerBI and Alteryx need a more robust integration with one another. 

 

Thanks again, 

Great @williamadams12. Glad it is helping. Always model in Power Query when possible, then analyze in DAX. Remember to mark complete if this gets you on your way so others can know what the solution was.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I find Power Query useful for certain things, but my data model is coming from an Alteryx abstraction from dozens of excel workbooks, so I may try the unpivot there first, but I've often found transposing and unpivoting data to be a little simpler and easier in Power Query, so I'll let  you know what happens. 

 

Thanks again for the quick response! 

Understood. Yeah, doing transformations at the source can help as well if that is possible. My point was, just don't try to do data modeling in the DAX model itself. 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
aj1973
Community Champion
Community Champion

Hi @williamadams12 

It looks like you need to inpivot your table first, for thast use Power query. and then try again.

 

or maybe you can share a Dummy PBIX and I will be more helpfull

 

Regards

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.