Facing issue in calculating confidence interval in power bi

[DDebbie]

Hello members,

I am facing an issue in calculating confidence level (95%) . I have calculated the standard deviation and I am finding the rolling average of 12 months. The confidence level when diced by products throws the below error message. My end goal is to find top range and botom range for each template over 12 months. Any leads on this is highly appreciated ! 

Top Range = [mean]+CONFIDENCE.NORM(0.05,[Standard Deviation],12)

Standard Deviation =

CALCULATE(

        STDEVX.P(

            DATESINPERIOD('Date'[FirstDayOfMonth], LASTDATE('Date'[FirstDayOfMonth]), -12, MONTH),

            'Standard Conversions'[Ford Efficiency]

        ),

        ALL('Date'[Year Month])

    )

[v-jtian-msft]

Hi,@DDebbie . 
Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[DDebbie]

Hi @freginier , there are no blank values and the data type for standard deviation is currency and that of Date [FirstDayOfMonth] is Date Time, which can't be changed as I am drawing the data from a power bi semantic model.

[v-jtian-msft]

Hi,@DDebbieI am glad to help you. 
Hello,@freginier ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Could you please check if [Standard Deviation] alone returns the correct structure, according to the error message it seems that [Standard Deviation] in the standard_dev function location is not returning the correct result.
Have you tried changing the "Date/Time" type to "Date" type?
You need to output the measure: Standard Deviation results separately to see if they are correct.
I would recommend running each step individually to check that it is working correctly.
If you can provide the pbix file without sensitive data, it will help to solve the problem, you'd better share the test data without sensitive data (I suggest to share it in your reply by copy and paste, not by screenshot) to avoid the uploaded link can't be accessed.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[freginier]

Hi @DDebbie before looking at DAX formula, do you check your data to be sure you have the correct type and no blank or something else?