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