Search code examples
powerbipowerquerypowerbi-desktopm

How to periodize Revenue over months

I am creating a Power BI Dashboard that should show revenue for a specific month.

I have some revenue that is for the specific month, no biggie. But I also have some revenue that we are spreading out over several months, a quarter or a year.

I want to see the actual revenue for a specific month considering this.

So for example, if this is my data:

Revenue StartDate EndDate
9000 2023-01-01 2023-03-31
10000 2023-02-01 2023-02-28

I want to see a this result

| Month | Revenue | | Jan | 3000 | | Feb | 13000 | | Mar | 3000 |

I dont know really where to start on this one and have nothing to show.

No need to solve this for me, I do enjoy creating the solution, just to point me in the right direction :)

Thanks!

Solution

  • You should be able to use a variation of the following.

    enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMFBQ0lEyMjAy1jUwBCIEz1jX2FApVidaydAARZURiiojXSMLpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Revenue = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Revenue", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each let
    a = List.Dates([StartDate], Number.From( [EndDate]-[StartDate]), #duration(1,0,0,0)),
    b = List.Transform(a, each Date.MonthName(_)),
    c = List.Distinct(b)
    in c
    ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Revenue.1", each [Revenue]/List.Count([Month])),
    #"Expanded Month" = Table.ExpandListColumn(#"Added Custom1", "Month"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Month",{"Revenue", "StartDate", "EndDate"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Month"}, {{"Revenue", each List.Sum([Revenue.1]), type number}})
in
    #"Grouped Rows"