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).
I’ve read through these two threads a couple of times, and want to make sure I understand the problem. Is it correct that you could use @Melissa’s PQ solution to fill in the missing dates, and that the problem occurs just when you try to call the STDEV.P function, which is not supported in direct query? If that’s the case, we can just build the standard deviation calculation from scratch using standard arithmetic operators over your specified date range in DAX.
Please let me know if I’ve got that right, and if so I’m happy to help you with that calculation.
When I tried the PowerQuery solution and went to apply the changes, I got this message:
The STDEV.P function seems to work fine over the data it is presented with in my Direct Query report. It seems the missing months for each Company/Product/Site/Warehouse combination needs to be generated programmatically in DAX somehow.
@pete962, you may want to take a look at this article, it details the limitations of using Direct Querry. One specific issue it calls out is the use of a date table (see below).
As mentioned in the article, you may want to see if there is a date table available in your datasource that you can pull from.
No built-in date hierarchy: When importing data, every date/datetime column will also have a built-in date hierarchy available by default. For example, if importing a table of sales orders including a column OrderDate , then upon using OrderDate in a visual, it will be possible to choose the appropriate level (year, month, day) to use. This built-in date hierarchy isn’t available when using DirectQuery. If there’s a Date table available in the underlying source, as is common in many data warehouses, then the DAX Time Intelligence functions can be used as normal.
Hi @pete962, did the response/training content provided by @heather and @brianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
I haven’t managed to resolve this query yet. I don’t know how to create the missing months and demand qty of 0 in DAX. I’ve tried Summarize and countrows to see if the number of rows per company/site/warehouse/product combination = 12…but then I don’t know how to use it to help me…so I’m a little confused at the moment
@AntrikshSharma
The logic needs to check that every month from the previous 12 exists for each combination of Company/Site/Warehouse/Product. So, as of current date, there should be a demand quantity for December 2019 - November 2020. If there is a month missing, a demand quantity of zero has to be created for that Company/Site/Warehouse/Product combination.
Doing this will ensure the correct STDEV.P calculation for demand quantity over the last 12 months.
I couldn’t find a way of doing this in Power Query or in DAX. I have ended up creating a new view in the backend ERP database that now shows demand across the 12 month date range.
