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

#### Chthonian

##### New Member
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Chthonian

##### New Member
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

##### Board Regular
See code below. Should do what you need

Code:
``````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),
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

##### New Member
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

##### Board Regular
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

Code:
``````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),
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"``````

Last edited: