I have to calculate the Inventory Holding Period.
The basic reasoning is that we have the Days Payable Outstanding (DPO), which provides the days the purchase was made. We calculate it as follows:
Last day of the current month = 01/31/2023, and DPO = 42
So, 01/31/2023 - 42 = 12/20/2022
We take the period as the days of the current month = 31
12/20/2022 - 31 = 11/19/2022
Now we have the period/interval 11/19/2022 - 12/20/2022. These are the months that have different stock variations.
Let's assume that on 11/19/2022, there was a variation of 30,000, and on 12/20/2022, there was a variation of 50,000.
This variation for the period should be the Cash Effect. However, as the period spans two different months, we should take a percentage of the variation from one month and add it to the percentage from the other month.
The calculation is as follows:
For 12/20/2022, with 31 days, we divide the variation by 31: 20/31 * variation, in this case, 50. This gives us a part of the value.
For the previous month, 11/19/2022, we take the remaining percentage (20/31 - 1) * variation * -1, where the variation is 30. This returns 100% of the value based on the stock variations for those months after summing the two. Resulting in the Cash Effect.
However, this method is not being accurate, as periods overlap or go uncalculated.
To address these possible errors, we have considered fixing the period to 30 days and DPO to the average of the year. Even so, there is a loss of data, resulting in an inaccurate Cash Effect.
Do you have any ideas to solve this problem, or any different method to perform this calculation?
Below is the DAX script where the Cash Effect calculation is being performed:
Cash Effect Inventory Variation Consolidated =
VAR DateVar = MAX('Calendar'[Date]) - [DPO]
// Calculation of Total Inventory DPO
VAR MonthVar = MONTH(DateVar)
VAR YearVar = YEAR(DateVar)
VAR TotalInventoryDPO =
CALCULATE(
[Total Inventory],
FILTER (
ALL('Calendar'),
'Calendar'[MonthN] = MonthVar &&
'Calendar'[Year] = YearVar
)
)
// Calculation of Inventory Variation
VAR InventoryVariation = TotalInventoryDPO - CALCULATE([Total Inventory DPO], DATEADD('Calendar'[Date], -1, MONTH))
// Calculation of Variation %
VAR Percentage = IF(DAY(DateVar) >= 30, InventoryVariation, (DAY(DateVar)/30) * InventoryVariation)
// Calculation of Total Inventory DPO LM (Last Month)
VAR TotalInventoryDPO_LM =
CALCULATE(
TotalInventoryDPO,
DATEADD('Calendar'[Date], -1, MONTH)
)
// Calculation of Variation % LM (Last Month)
VAR PercentageLM =
VAR PercentageLM = (DAY(DateVar)/30) - 1.0
VAR VariationLM = CALCULATE([Inventory Variation], DATEADD('Calendar'[Date], -1, MONTH))
RETURN IF(DAY(DateVar) >= 30, 0, VariationLM * PercentageLM)
// Final Calculation of Cash Effect Inventory Variation Consolidated
RETURN Percentage + PercentageLM
