Writing Rows in a Calculated Table From Flat Data, and Building a Line Graph with the Results

[learningdax]

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.

[ChrisMendoza · Accepted solution]

@learningdax -

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.

[learningdax]

This is exactly what I was trying to do - thank you so much 🙂