Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  3. #3
    Board Regular
    Join Date
    Nov 2017
    Posts
    65
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    See code below. Should do what you need

    Adam

    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),
        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"

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  5. #5
    Board Regular
    Join Date
    Nov 2017
    Posts
    65
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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),
        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"
    Last edited by horseyride; Jun 12th, 2019 at 07:47 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •