The accounts payable turnover ratio is calculated using the following formula:
To get the total purchases for the year, we simply need to sum the rows in the table that have a categorization of Purchases. We do this using the SUMX DAX function. With this, we filter our base table, R03_Table, where the Category column has a value of Purchases, and then sum the Amount column.
In order to calculate the average accounts payable for the year, we need to find our opening and closing balances for the year. We calculate the opening balance, __OpeningAP, by filtering our base table, R03_Table, where the Category column has a value of Accounts Payable and the Date column has the minimum date within the current context. Since this is a measure, we need to use aggregation (in this case, provided by SUMX), but since we are returning a single row, we could have used a different aggregation function, such as MAXX or MINX. Similarly, we calculate the closing balance, __ClosingAP, by filtering our base table, R03_Table, where the Category column has a value of Accounts Payable and the Date column has the maximum date within the current context.
To arrive at the average between the opening and closing accounts payable balances, __AverageAP, we simply need to DIVIDE the sum of our __OpeningAP and __ClosingAP balances by 2.
Once we have calculated our __Purchases and __AverageAP variables, we can employ our accounts payable turnover ratio formula and simply divide __Purchases by __AverageAP.