Forum Discussion
Calculated Measure Using Multiple Dates and Multiple Percentages
- 6 years ago
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:
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
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?
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.
- Anonymous6 years agoNot applicable
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,