Forum Discussion
Including 0 values when calculating STDEV
Hi, Let me walk you through a better example. I am attempting to determine the most recent 6-Month STDEV for sales by product in order to determine variation. The Sales Data is made up of dai...
Anonymous Maybe something like this, PBIX is attached below signature. I'm not getting your stated standard deviation values but I added a debugging measure into the PBIX that should help you determine what is going on. With the debugging measure you can see that it is returning 0's for empty months.
Measure StdDev = VAR __Date = MAX('Table'[Date]) VAR __BeginDate = EOMONTH(__Date,-6) VAR __EndDate = EOMONTH(__Date, -1) VAR __Table = FILTER(ALL('Table'),[Date]>=__BeginDate && [Date]<=__EndDate) VAR __Count = COUNTROWS(__Table) VAR __Missing = 6 - __Count VAR __MissingTable = SELECTCOLUMNS( ADDCOLUMNS( GENERATESERIES(1,__Missing,1), "rmv",0 ), "rmv",[rmv], "Date",[Value] ) VAR __FinalTable = UNION(__Table, __MissingTable) VAR __Result = STDEVX.P(__FinalTable,[rmv]) RETURN __Result
Hi Greg_Deckler ,
Here is the sample pbix. Please select sep 2022(in the slicer), so that we will have enough data. https://1drv.ms/u/s!AlFPrnIF0lWCfNoRDLODUZdzBvs?e=ruT6bi
Thanks
Anonymous So the Data table should be this?
| Date | rmv |
| Jun-20 | 3 |
| Sep-20 | 13 |
| Oct-20 | 1 |
| Nov-20 | 5 |
| Dec-20 | 7 |
| Jan-21 | 6 |
| Feb-21 | 3 |
| Mar-21 | 4 |
| Apr-21 | 7 |
| May-21 | 1 |
| Jun-21 | 4 |
| Jul-21 | 3 |
| Aug-21 | 1 |
| Sep-21 | 2 |
| Oct-21 | 1 |
| Nov-21 | 1 |
| Dec-21 | 4 |
| Jan-22 | 7 |
Hi Greg_Deckler .
The below link(has pbix) has perfect my kind of scenario. https://1drv.ms/u/s!AlFPrnIF0lWCfNoRDLODUZdzBvs?e=ruT6bi
Thanks