Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
lennox25
Post Patron
Post Patron

Std Deviation measure continuous regardless of months

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
 ))
8 REPLIES 8
v-jiewu-msft
Community Support
Community Support

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
        )
    )

vjiewumsft_0-1726218979100.png

 

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.

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.

lennox25_0-1726229842727.png

 

rajendraongole1
Super User
Super User

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
)
)

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

lennox25_0-1725983728160.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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.

lennox25_0-1725984133668.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.