I have come across a problem when creating a measure using STDEV.P…
The requirement is to calculate standard deviation (STDEV.P) for demand quantity per item over the previous 12 months, the month range being between (CurrentMonth-12) and (CurrentMonth-1). In order for the standard deviation calculation to work correctly, the demand quantity for every month in that range should be present against each Company/Site/Warehouse/Product combination. But, the problem I have is that the historic demand data from the ERP database does not include months if the product has zero demand quantity.
In the attached PBI, the tables on the left shows the current situation. You will notice that there are missing months. The tables on the right shows the desired results when all months in the range are present with a demand quantity of zero. Is there a method in DAX to detect this problem and correct the calculation?
An important thing to note is that the datasource is Direct Query, and there doesn’t appear to be any transformation that will do this in Power Query (@Melissa has already come up with a solution, but sadly it wasn’t a valid solution for a Direct Query report).
Thanking you in advance,