Denmark - Power BI User Group

Expand all | Collapse all

Help on calculate project carry-over in DAX

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

    Posted 28 days ago
      |   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
    ------------------------------

    Attachment(s)



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

    Gold Contributor
    Posted 28 days ago
    Hi Fu,

    I would use M/PowerQuery instead

    let
    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]))
    in
    #"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
    Denmark
    es@catmansolution.com
    ------------------------------



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

    Posted 28 days ago
    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
    ------------------------------