locked
Group values based on relationship to dates RRS feed

  • Question

  • Hi All,

    I am very new to power pivot so please bear with me.

    I have created a model which evaluates how long it takes to collect payments relative to the invoice due date. For each invoice due date month, I have a calculated field which gives me the percent collected in each month. Now I want each months results to be grouped based on the time it took from the due date month. See below. the top left 3% means that 3% of invoices with a 1/1/2015 due date were collected in dec-14 (1 month before (t-1) the due date). similarly, the 6% should be in T-1 as it means that 6% of invoices with a Feb-15 due date was collected one month prior to the due date. where the due date and payment date are equal, the value should be grouped into T0 (ex. 37% in row 3 column 2 & 48% in row 4 column 3) ..and so on. I also posted the full result i expect to see below. I hope this is clear.

    % Collected in Due Month Due_date              
    Payment_Due_Date 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015
    12/1/2014 3% 1%            
    1/1/2015 37% 6% 0%          
    2/1/2015 42% 48% 4% 0% 0%      
    3/1/2015 7% 33% 33% 9%        
    4/1/2015 3% 6% 15% 47% 5%      
    5/1/2015 1% 3% 25% 38% 36% 3% 0%  
    6/1/2015 1% 1% 2% 9% 30% 57% 6%  
    7/1/2015 0% 0% 1% 1% 7% 38% 49% 10%
    8/1/2015 0% 3% 0% 0% 5% 3% 32% 44%
    9/1/2015 0% 0% 0% 1% 1% 2% 8% 34%
    10/1/2015       0% 0% 6% 4% 9%
    11/1/2015                
    Grand Total 94% 102% 80% 104% 85% 109% 98% 97%

    this is the result I am trying to get. what DAX formulas can i use to automate this?

     Collection Time 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015
    T-2   1% 0% 0% 0% 0% 0% 0%
    T-1 3% 6% 4% 9% 5% 3% 6% 10%
    T0 37% 48% 33% 47% 36% 57% 49% 44%
    T1 42% 33% 15% 38% 30% 38% 32% 34%
    T2 7% 6% 25% 9% 7% 3% 8% 9%
    T3 3% 3% 2% 1% 5% 2% 4% 0%
    T4 1% 1% 1% 0% 1% 6% 0%  
    T5 1% 0% 0% 1% 0% 0%    

    Thanks in advance for the help

    Thursday, November 12, 2015 6:10 PM

Answers

  • Hi mflank,

    welcome to Power Pivot! It's going to be quite an adventure...

    You're not saying much about what your model looks like, I'm assuming you have two tables with the months, Due_Date and Payment_Due_Date, as well as a calculated field [PercentCollected]. As you have Tx labels in the result, you will also need a table with these, say table Collection with a column [CollectionTime]. The Due_Date and Payment_Due_Date should have a sequence column, like below:

    Í'm not assuming that you only use the output for one value of Collection[CollectionTime] and one value of Due_Date[Month] at a time, so the calculated field you're looking for has to work also for multiple values selected. Therefore we'll use a double SUMX over both Collection and Due_Date. Technically, SUMX will not return the correct results here, as percentages cannot simply be summed; you should use appropriate calculations to take care of this.

    Let's start with a calculation that sums all percentages for each combination of Due_Date and Payment_Due_Date. You can use CROSSJOIN to create a table with each combination of values in both tables (it will contain only the combinations of rows that are selected in the current context):

    [TotalPercentage]:= SUMX(CROSSJOIN(Due_Date,Payment_Due_Date),[PercentCollected])

    Now, for a value of Collection[CollectionTime] we can filter to the appropriate combination of Due_Date and Payment_Due_Date rows before summing [PercentCollected]. We use SUMX over Collection to be able to have multiple CollectionTime values:

    [CollectionTimePercentage]:=SUMX(Collection,CALCULATE(SUMX(FILTER(CROSSJOIN(Due_Date,Payment_Due_Date),Payment_Due_Date[Sequence] = Due_Date[Sequence]+Collection[Delay]),[PercentCollected])))

    I'm quite sure there are more efficient solutions, but it would help to know more about your model.

    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:47 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:39 AM
    Thursday, November 12, 2015 9:25 PM
    Answerer

All replies

  • Hi mflank,

    welcome to Power Pivot! It's going to be quite an adventure...

    You're not saying much about what your model looks like, I'm assuming you have two tables with the months, Due_Date and Payment_Due_Date, as well as a calculated field [PercentCollected]. As you have Tx labels in the result, you will also need a table with these, say table Collection with a column [CollectionTime]. The Due_Date and Payment_Due_Date should have a sequence column, like below:

    Í'm not assuming that you only use the output for one value of Collection[CollectionTime] and one value of Due_Date[Month] at a time, so the calculated field you're looking for has to work also for multiple values selected. Therefore we'll use a double SUMX over both Collection and Due_Date. Technically, SUMX will not return the correct results here, as percentages cannot simply be summed; you should use appropriate calculations to take care of this.

    Let's start with a calculation that sums all percentages for each combination of Due_Date and Payment_Due_Date. You can use CROSSJOIN to create a table with each combination of values in both tables (it will contain only the combinations of rows that are selected in the current context):

    [TotalPercentage]:= SUMX(CROSSJOIN(Due_Date,Payment_Due_Date),[PercentCollected])

    Now, for a value of Collection[CollectionTime] we can filter to the appropriate combination of Due_Date and Payment_Due_Date rows before summing [PercentCollected]. We use SUMX over Collection to be able to have multiple CollectionTime values:

    [CollectionTimePercentage]:=SUMX(Collection,CALCULATE(SUMX(FILTER(CROSSJOIN(Due_Date,Payment_Due_Date),Payment_Due_Date[Sequence] = Due_Date[Sequence]+Collection[Delay]),[PercentCollected])))

    I'm quite sure there are more efficient solutions, but it would help to know more about your model.

    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:47 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:39 AM
    Thursday, November 12, 2015 9:25 PM
    Answerer
  • What does your data model look like? Are Payment_Due_Date and Due_Date in the same table or different tables? If they are in the same table you probably don't need to do the crossjoin that Michiel suggested, you could probably create a simple calculated column to find the number of days between each date and then either use the dynamic segmentation technique (see www.daxpatterns.com/dynamic-segmentation/) or create a collection time table and setup a relationship between that and the calcuated column

    http://darren.gosbell.com - please mark correct answers

    Friday, November 13, 2015 12:14 AM
  • Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013/2016 Pro Plus.
    Perform a secondary Power Query operation on the PowerPivot results.
    http://www.mediafire.com/download/6t121gcf9jbbsfm/11_12_15a.xlsx

    Friday, November 13, 2015 4:19 AM