Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
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.
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.
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
)
)
Proud to be a Super User! | |
Hi @rajendraongole1 , Your calculation is Std Dev 2 - it has had strange results. Heres my sample data.
Can you please copy and paste the text here? with expected result
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.
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. |
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |