Forum Discussion
Writing Rows in a Calculated Table From Flat Data, and Building a Line Graph with the Results
Hi,
I'm new to writing dax in Power BI, and I have a need to convert hard data that looks like this into a monthly line graph going out from the task finish date to the number of months in the savings duration column. These are all hard columns from tables except the monthly savings, which is a calculated column (Savings / SavingsDuration).
| ID | TaskFinishDate | Savings | SavingsDuration | Monthly Savings |
| 18-05 | 4/15/2019 | 500 | 12 | 41.67 |
| 18-02 | 5/1/2019 | 322 | 8 | 40.25 |
| 18-027 | 1/1/2019 | 300 | 12 | 25 |
| 18-0108 | 7/1/2019 | 2200 | 12 | 183.33 |
| 18-005 | 10/4/2019 | 162 | 12 | 13.50 |
| 18-029 | 6/3/2019 | 120 | 1 | 120 |
| 18-0272 | 3/1/2019 | 610 | 1 | 610 |
| 18-0244 | 2/1/2019 | -518 | 11 | -47.09 |
For example, the first savings row (projectidentifier 18-0599-00) starts April 15, 2019 and goes out 12 months with the monthly savings of $41,167 (5/15/2019, 6/15/2019, 7/15/2019, etc through 4/15/2020).
Each project ID could represent a seperate line, with a total line or something like it.
I've been at this several days attempting to create a calculated table that add rows based on the number of months in the SavingsDuration column, and then using the calculated table fields in visual, but I guess I just don't know dax well enough to get this done.
Any help on this, even helping me define the approach, would be greatly appreciated.
Thank you in advance.
Anonymous -
See if this gets you close to what you're going for:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfi2wUNRO4tx/zUKJhZ/zOUeyJwESlQZypLxWtac7YXQSpPEC8axIl6MTcWnLrrXqsUeJnKjesxcj6JJYLPYuPwme3FT9yvxaUPQ8CNBj8IbGK4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectID = _t, FinishDate = _t, Savings = _t, SavingsDuration = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"FinishDate", type date}, {"Savings", Int64.Type}, {"SavingsDuration", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "AssignPeriods", each {1..[SavingsDuration]}), #"Expanded AssignPeriods" = Table.ExpandListColumn(#"Added Custom", "AssignPeriods"), #"Added Custom1" = Table.AddColumn(#"Expanded AssignPeriods", "Monthly Savings", each [Savings] / [SavingsDuration]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each if [AssignPeriods] = 1 then [FinishDate] else Date.AddMonths([FinishDate],[AssignPeriods] - 1)) in #"Added Custom2"Throw it into the Advanced Editor of a Blank Query.
2 Replies
Anonymous -
See if this gets you close to what you're going for:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfi2wUNRO4tx/zUKJhZ/zOUeyJwESlQZypLxWtac7YXQSpPEC8axIl6MTcWnLrrXqsUeJnKjesxcj6JJYLPYuPwme3FT9yvxaUPQ8CNBj8IbGK4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectID = _t, FinishDate = _t, Savings = _t, SavingsDuration = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"FinishDate", type date}, {"Savings", Int64.Type}, {"SavingsDuration", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "AssignPeriods", each {1..[SavingsDuration]}), #"Expanded AssignPeriods" = Table.ExpandListColumn(#"Added Custom", "AssignPeriods"), #"Added Custom1" = Table.AddColumn(#"Expanded AssignPeriods", "Monthly Savings", each [Savings] / [SavingsDuration]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each if [AssignPeriods] = 1 then [FinishDate] else Date.AddMonths([FinishDate],[AssignPeriods] - 1)) in #"Added Custom2"Throw it into the Advanced Editor of a Blank Query.