Power Pivot Principles: COGS and COGS Cash Payments
25 May 2021
Welcome back to the Power Pivot Principles blog. This week, we are going calculate Cost of Goods Sold and cash payments for Cost of Goods Sold.
Imagine the management of XYZ company (say) is interested in knowing the cash amount of costs of goods sold (COGS). We will first need to calculate the accrued Cost of Goods Sold (COGS). My data includes all transactions belonging to the chart of accounts (COA) group ‘T’ as COGS. To calculate this, we’ll use the CALCULATE function:
=CALCULATE(SUM('Transaction'[Actual_Amount]), COA[Group]="T")
Let’s assume that COGS will be paid in 30 days. To calculate the cash payments for COGS, I will create a measure by using the above parameter and the DATEADD function.
=CALCULATE([Cost of Goods Sold (COGS)], DATEADD('Calendar'[Date], -30,DAY))
Let me pull in the measures created above in a PivotTable, viz.
The company can now differentiate between Cost of Goods Sold and Cash Payments on a monthly basis.
That’s it for this week!
Come back next week to see how we use the disconnected table for performing a sensitivity analysis by staying tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
