I would like help discovering why a disconnected slicer is not returning the expected data. Hopefully I’m missing something obvious.

I would like to return a payment schedule based on our Accounts Payable (AP) groupings. The days to pay bucket is the Payment Date (if one exists) - Accounting Date (what we call the Invoice Date).

<tbody>

</tbody>

I have a disconnected table ‘AP Intervals’ with the AP text groupings along with Min and Max columns for the number of days in the group. For example

<tbody>

</tbody>

Our vouchers table has the following structure. Note that the full check total is returned by our AP system for each row so I use the Voucher total rather than Check total.

<tbody>

</tbody>

These are the relevant measures I am using.

When I create the pivot table and use the ‘AP Intervals’ [Duration] as the row and [Payment By Period] as the value the measure returns the same total for all the AP Interval categories instead of grouping it. I'm not sure if it's something about the Average measure that is causing the difference but I've used this format in the past with no problem. Perhaps I need to group by the Check Number but I'm not sure how the measure would be created.

I would like to return a payment schedule based on our Accounts Payable (AP) groupings. The days to pay bucket is the Payment Date (if one exists) - Accounting Date (what we call the Invoice Date).

Nov 2017 | Dec 2017 | Jan 2017 | |
---|---|---|---|

< 7 days | $$$ | $$ | $$ |

7-10 days | $ | $ | $$ |

11-14 days | $$ | $$$ | $$ |

etc |

<tbody>

</tbody>

I have a disconnected table ‘AP Intervals’ with the AP text groupings along with Min and Max columns for the number of days in the group. For example

Duration | Min | Max |
---|---|---|

< 7 days | 7 | |

7-10 days | 7 | 10 |

11-14 days | 10 | 14 |

etc |

<tbody>

</tbody>

Our vouchers table has the following structure. Note that the full check total is returned by our AP system for each row so I use the Voucher total rather than Check total.

Invoice No | Accounting Date | Voucher Amount | Check No. | Payment Date | Check Amount |
---|---|---|---|---|---|

A01 | 6/7/17 | $40 | 001 | 6/20/17 | $100 |

A02 | 6/12/17 | $20 | 001 | 6/20/17 | $100 |

A03 | 6/13/17 | $40 | 001 | 6/20/17 | $100 |

etc |

<tbody>

</tbody>

These are the relevant measures I am using.

Code:

```
Avg Voucher Pay Days:=
[SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]AVERAGEX[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers,[/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]IF[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]ISBLANK[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers[Payment Date]),[/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]BLANK[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](),Vouchers[Payment Date]-Vouchers[Accounting Date]))[/SIZE]
```

Code:

```
Paid Amt:=
[SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]CALCULATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]SUM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers[Voucher Amount]),Vouchers[Check Amount]>0)[/SIZE]
```

Code:

```
Payment By Period:=CALCULATE (
[Paid Amt],
FILTER(
'AP Intervals',
[Avg Voucher Pay Days] >= 'AP Intervals'[Min]
&& [Avg Voucher Pay Days] < 'AP Intervals'[Max]
)
)
```

Last edited: