Answered by:
Group values based on relationship to dates
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 dec14 (1 month before (t1) the due date). similarly, the 6% should be in T1 as it means that 6% of invoices with a Feb15 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 T2 1% 0% 0% 0% 0% 0% 0% T1 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 PMAnswerer
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 PMAnswerer 
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/dynamicsegmentation/) 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 AddIn.
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