Std Deviation measure continuous regardless of months

[lennox25]

I have this measure, its for 12 months only Apr to March. Can someone help me with adjusting it so that it rolls on to how ever many months are in my table?

Std dev =

Var InitialDate =

If(

    month(SELECTEDVALUE('month avg'[Month Year])) >= 4

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year])),04,01)

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year]))-1,04,01)

)

Var FinalDate =

If(

    month(SELECTEDVALUE('month avg'[Month Year])) >= 4

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year]))+1,03,31)

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year])),03,31))

Return

 CALCULATE(STDEV.P('month avg'[Total Sales Per Month])

 ,ALLSELECTED('month avg'[Month Year])

 ,DATESBETWEEN(

    'month avg'[Month Year]

    ,InitialDate

    ,FinalDate

 ))

[v-jiewu-msft]

Hi @lennox25 ,

Based on the description, try to use the following DAX formula to calculate std.

Std dev2 = 
VAR CurrentDate = MAX('Date_Dim'[MonthYearInt])
VAR MonthsBack = 12
VAR StartDate = EDATE(CurrentDate, -MonthsBack + 1)
RETURN
    CALCULATE(
        STDEV.P('Date_Dim'[Total_Sales_only]),
        FILTER(
            ALL('Date_Dim'[MonthYearInt]),
            'Date_Dim'[MonthYearInt] >= StartDate && 'Date_Dim'[MonthYearInt] <= CurrentDate
        )
    )

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lennox25]

hi @v-jiewu-msft  I appreciate the work that you have put in here, but I need the std d to be the same month on month  - with my neasure above, I can only acheive the following. As you can see its only doing STD D to each financial year I need it to be the same month on month.

[rajendraongole1]

Hi @lennox25 -  Try like below with min and max on month year field. if not works , Can you share sample data Or a sample pbix after removing sensitive data.

Std dev =
VAR InitialDate =
MIN('month avg'[Month Year]) 
VAR FinalDate =
MAX('month avg'[Month Year])
RETURN
CALCULATE(
STDEV.P('month avg'[Total Sales Per Month]),
ALLSELECTED('month avg'[Month Year]),
DATESBETWEEN(
'month avg'[Month Year],
InitialDate,
FinalDate
)
)

[lennox25]

Hi @rajendraongole1 , Your calculation is Std Dev 2 - it has had strange results. Heres my sample data. 

[rajendraongole1]

Can you please copy and paste the text here? with expected result

[lennox25]

OK, ignore the prrevious example. I will use my excel example.  I can use a separate date table if that helps (which is Date_Dim) or I have added in PBI a date table to my sales table.

[rajendraongole1]

Hi  - Expecting the input should be copy and paste as like below: 

Date Dim(MonthYear)

4/23

5/23

6/23

7/23

8/23

9/23

10/23

11/23

12/23

1/24 Please share the inputs as like above for easy copy text.

[lennox25]

Date_Dim[MonthYear] Date_Dim[MonthYearInt] [Total_Sales_only] Avg Std Dev
Apr 23 202304 67076 73,756 8,516
May 23 202305 75713 73,756 8,516
Jun 23 202306 63500 73,756 8,516
Jul 23 202307 62006 73,756 8,516
Aug 23 202308 73358 73,756 8,516
Sep 23 202309 61440 73,756 8,516
Oct 23 202310 76631 73,756 8,516
Nov 23 202311 88816 73,756 8,516
Dec 23 202312 77698 73,756 8,516
Jan 24 202401 86106 73,756 8,516
Feb 24 202402 78599 73,756 8,516
Mar 24 202403 74128 73,756 8,516
Apr 24 202404 78584 73,756 8,516
May 24 202405 81704 73,756 8,516
Jun 24 202406 70347 73,756 8,516
Jul 24 202407 82884 73,756 8,516