Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

90 days Utilization

Hi,     Please find the attached sheet as sample data, please help me with same results in BI Please   Sample Data 
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-lili6-msft 

Sorry wrong fike posted

 

Updated smaple Data 

 

Also see the results below and there are graphs in file, i wish to be have same graph as well 

 

Please help

 

 

 

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi Anonymous 

First, there is a mistake in your excel file.

=IF([@Day] = "Mon", [@WeekOf], IF([@Day] = "Tue", [@WeekOf]+1, IF([@Day] = "Wed", [@WeekOf]+2, IF([@Day] = "Thur", [@WeekOf]+3, [@WeekOf]+4))))

In your Day column, it is Thu not Thur.

please adjust it and refresh the excel file.

 

Second, then create a 90% Percentile (90 Days) column by this formula:

90% Percentile = var _90date=MINX(TOPN(91,FILTER(Table1,Table1[Actual Date]<EARLIER(Table1[Actual Date])),Table1[Actual Date],DESC),Table1[Actual Date]) return
CALCULATE(AVERAGE(Table1[Avg Weekly]),FILTER(Table1,Table1[Actual Date]<EARLIER(Table1[Actual Date])&&Table1[Actual Date]>=_90date))*0.9

Result:

 

and here is sample pbix file, please try it.

 

Regards,

Lin