Microsoft BI Professionals Denmark

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Help on calculate project carry-over in DAX

  • 1.  Help on calculate project carry-over in DAX

    Posted Feb 18, 2019 05:30 AM
      |   view attached
    ​Hi all,

    I have a calculation question and I have been struggling to make it happen in Power BI calculation. So here's the discription break down step by step:
    1. we are running several sales projects, some of which are closed won, some are still open
    2. for all the projects, we expect to have a carry over effect lasting for 12 months starting from the project close date, and each month get 1/12 of the value. Note, both closed and open projects apply to this methodology
    3. Calculate the carry over value for each month: either 0 or 1/12 of the project value.

    See the excel sheet as example: quite straight forward in excel, but I can't make it in Power BI... Thanks a million...

    Fu Chen
    Chr. Hansen A/s


  • 2.  RE: Help on calculate project carry-over in DAX

    Gold Contributor
    Posted Feb 18, 2019 05:44 AM
    Hi Fu,

    I would use M/PowerQuery instead

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcpBCoAgEEbhq8isNf6ZtPQW7cVVtGkTRPenlCAa3+7BlzMt57Fv62WYLBmWAYCpPQt2zE7AkYr9pDTplYRUmX5ybHLSMvTSNxm1nHsZqhRomV5Zbg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sale projects" = _t, value = _t, #"Close date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale projects", type text}, {"value", type number}, {"Close date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 12),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Length"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each {0..[Length]-1}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Added Custom2" = Table.AddColumn(#"Expanded {0}", "MonthlyValue", each [value]/[Length]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Date", each Date.AddMonths([Close date],[Custom]))
    #"Added Custom3"

    This Query will transform your project table to a value table with a record for each month and if you for some reason need to modify the length of each project I added a custom column with 12 for each - and that column is used to create the length of the carry over.

    And the new date column can then be used to relate to your time table

    Hope this can help - other wise feel free to reach out

    Erik Svensen
    PUG Leader

  • 3.  RE: Help on calculate project carry-over in DAX

    Posted Feb 18, 2019 08:04 AM
    That's a very interesting solution! Basically, you expand one row into 12 rows and give monthly value and time on each of them: in this way, it makes possible to report on the carryover. I just tested in my work and works. Well done!

    The only minor disadvantage on working on the query is that this makes my model twice as big because I need to do this in a duplicated query (there are many other calculations in the original query), and it makes the refreshing even longer. It's not a problem on scheduled refresh but takes a lot of patience to change the model...

    Thanks again, that's very inspirational!

    Fu Chen
    Chr. Hansen A/s