Confidence Level (95%)

rendalignacio

Here the solution is shown...

First Picture: Report

Second Picture: Sample Data Per Date

Third Picture: Sample Data Per Month

First, I created a new table (Per Month) based of the original table (Per Date).

On the Ribbon: Modeling Tab --> New Table    then...

Per Month = SELECTCOLUMNS('Per Date';"Type";'Per Date'[Type];"Month";FORMAT('Per Date'[Date];"MMMM");"Month Number";FORMAT('Per Date'[Date];"M");"Data 18";'Per Date'[Data 18]) 

Note: To Sort the Month Column, select it and go to the Ribbon: Modeling Tab --> Sort By column (Choose Month Number Column)

Measures:

Per Date Table:

Confidence Level 95% = 1.96

Mean = var ty = FIRSTNONBLANK('Per Date'[Type];1) var da = FIRSTNONBLANK('Per Date'[Date];1) return    
                 CALCULATE(AVERAGE('Per Date'[Data 18]);ALL('Per Date');'Per Date'[Type] = ty;'Per Date'[Date] = da)

std Deviation = var ty = FIRSTNONBLANK('Per Date'[Type];1) var da = FIRSTNONBLANK('Per Date'[Date];1) return
                CALCULATE(STDEV.P('Per Date'[Data 18]);ALL('Per Date');'Per Date'[Type] = ty;'Per Date'[Date] = da)

std error of mean = var ty = FIRSTNONBLANK('Per Date'[Type];1) var da = FIRSTNONBLANK('Per Date'[Date];1) return  
                    CALCULATE(DIVIDE([std Deviation];SQRT(COUNTROWS(ALL('Per Date'))));'Per Date'[Type] = ty;'Per Date'[Date] = da)

Lower Limit = [Mean] - [Confidence Level 95%]*[std error of mean]

Upper Limit = [Mean] + [Confidence Level 95%]*[std error of mean]

Out or Within = IF(AND(FIRSTNONBLANK('Per Date'[Data 18];1) >= [Lower Limit];FIRSTNONBLANK('Per Date'[Data 18];1) <= [Upper Limit]);"Within";"Out")

Per Month Table:

Confidence Level 95% 2 = 1.96

Mean 2 = var ty = FIRSTNONBLANK('Per Month'[Type];1) var da = FIRSTNONBLANK('Per Month'[Month];1) return    
                 CALCULATE(AVERAGE('Per Month'[Data 18]);ALL('Per Month');'Per Month'[Type] = ty;'Per Month'[Month] = da)

std Deviation 2 = var ty = FIRSTNONBLANK('Per Month'[Type];1) var da = FIRSTNONBLANK('Per Month'[Month];1) return
                CALCULATE(STDEV.P('Per Month'[Data 18]);ALL('Per Month');'Per Month'[Type] = ty;'Per Month'[Month] = da)

std error of mean 2 = var ty = FIRSTNONBLANK('Per Month'[Type];1) var da = FIRSTNONBLANK('Per Month'[Month];1) return  
                    CALCULATE(DIVIDE([std Deviation 2];SQRT(COUNTROWS(ALL('Per Month'))));'Per Month'[Type] = ty;'Per Month'[Month] = da)

Lower Limit 2 = [Mean 2] - [Confidence Level 95% 2]*[std error of mean 2]

Upper Limit 2 = [Mean 2] + [Confidence Level 95% 2]*[std error of mean 2]

Out or Within 2 = IF(AND(FIRSTNONBLANK('Per Month'[Data 18];1) >= [Lower Limit 2];FIRSTNONBLANK('Per Month'[Data 18];1) <= [Upper Limit 2]);"Within";"Out")

For enhancing the solution, you can analyze the number of "within" by type, date , etc. Try this...

1st Part

2nd Part

3th Part

4th Part

Regards

BILASolution