Calculate Active Billing Values in a given month (Power Pivot)

[Chthonian]

Hi all,

I have been hunting for an answer to this for days and just getting nowhere

We have a table of rental service charges that have 4 key Fields;

1. Start Date
2. End Date
3. Quantity
4. Sell Price

I have created a Date (calendar) Table and created a relationship to both the Start Date & End Date fields on my service charge table. So here is the logic;

• If a charge has no end date it is still billing since its start date
• If there is an end date it has stopped billing so there would be no charge in the month it was end dated
• Start and end dates can be any date in a 3 year period

I have been tasked with calculating what the service charge total value was in each month since records began 3 years ago. I have been trying to create a measure or calculated column that will allow me to calculate the total value in that month (i.e. a start date was in that month or before that month and that the charge was not end dated in that month or that the end date is blank)

Can anybody please assist? I have been getting nowhere fast with this, most likely as I am new to power pivot :P

Thanks a million in advance Gents and Ladies,
David

 

[Chthonian]

Was just thinking about my description and wanted to add a small note:

Each service charge is only billed once per month. Think of a broadband connection with no contract, we know when it started, we know if it stopped, and we know if it is still active but the only dates we have to work with are the Start & End Dates. And I need to summarize and plot this on a graph over time.

 

[horseyride]

See code below. Should do what you need

Adam

let
    //Read in table of [Start, End, Qty, Price]
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"End", type date}, {"Qty", Int64.Type}, {"Price", Int64.Type}}),
    PQList = Table.AddColumn(#"Changed Type", "PriceXQty", each [Price]*[Qty]),

    //Generate all months between two years
    Years = Table.FromList({2012..2020}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
    AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
    ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
    CreateDate = Table.AddColumn(ShowMonths, "Date", each #date([Column1],[Month],1)),
    #"Removed Other Columns" = Table.SelectColumns(CreateDate,{"Date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}}),

    //Merge all combinations, use PricexQuantity for any start-end date range that falls into that calendar month
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each PQList),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"start", "End", "PriceXQty"}, {"start", "End", "PriceXQty"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [start]<=Date.AddDays( Date.AddMonths([Date],1),-1) and ([End]=null or [End] > [Date]) then [PriceXQty] else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] <> 0)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {{"Total", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

 

[Chthonian]

Thanks a million Adam, I truly appreciate your effort. The only question now is how do I use that code? Do I just append that to the end of the code I have in the Power Query Advance Editor?

The data I am using is coming from a Table on a network DB so I already have some m-code in there doing its bits and pieces on the Service Charges to create my fact table.

Thanks again!

 

[horseyride]

Easiest is to use this as a new query and just reference the output from the other query in this one, otherwise we may be using same system generated variable names. Remove the changedtype row as well if the columns have correct type. So if existing code is in a query named Query1 then code would be as below to reference it. Remember the structure being expected as input has four columns named Start, End, Qty and Price. You might want to insert a step to rename your columns if necessary

let
    //Read in table of [Start, End, Qty, Price]
    Source = Query1,
    PQList = Table.AddColumn(Source, "PriceXQty", each [Price]*[Qty]),

    //Generate all months between two years
    Years = Table.FromList({2012..2020}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
    AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
    ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
    CreateDate = Table.AddColumn(ShowMonths, "Date", each #date([Column1],[Month],1)),
    #"Removed Other Columns" = Table.SelectColumns(CreateDate,{"Date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}}),

    //Merge all combinations, use PricexQuantity for any start-end date range that falls into that calendar month
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each PQList),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"start", "End", "PriceXQty"}, {"start", "End", "PriceXQty"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [start]<=Date.AddDays( Date.AddMonths([Date],1),-1) and ([End]=null or [End] > [Date]) then [PriceXQty] else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] <> 0)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {{"Total", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"