90 days Utilization

[Amardeep100115]

Hi,

Please find the attached sheet as sample data, please help me with same results in BI Please

Sample Data 

[Amardeep100115]

I have shared sample data which i am refering in my report, Please help me have same result

wish to be have weekly average cust# count, Monthly average Cust# count and its 90days pecentile (90 days) like you created but it should be measure if possible

Thanks in advance 

like i have in excel 

Sample Data 

Please

[Amardeep100115]

I have shared sample data which i am refering in my report, Please help me have same result

wish to be have weekly average cust# count, Monthly average Cust# count and its 90days pecentile (90 days) like you created but it should be measure if possible

Thanks in advance 

like i have in excel 

Sample Data 

Please

[v-lili6-msft]

hi @Amardeep100115 

the sample data is so big and it is raw data, could you please share a simple pbix file and your expected output.

Regards,

Lin

[Amardeep100115]

Hi @v-lili6-msft 

Thanks you so much for your greate help 
Please have a look attached data in which iave give Pine Code, Date, Cust #.
wish to be have weekly average cust# count, Monthly average Cust# count and its 90days pecentile (90 days) like you created but it should be measure if possible

Thanks in advance 

Revised Sample Data 

[v-lili6-msft]

hi  @Amardeep100115 

If you want a meausre, adjust the formula as below:

Measure 2 =
VAR _90date =
    MINX (
        TOPN (
            91,
            FILTER ( ALL ( Table1 ), Table1[Actual Date] < MAX ( Table1[Actual Date] ) ),
            Table1[Actual Date], DESC
        ),
        Table1[Actual Date]
    )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( Table1 ),
            Table1[Actual Date] < MAX ( Table1[Actual Date] )
                && Table1[Actual Date] >= _90date
        ),
        [Avg Weekly]
    ) * 0.9

and this will lead a measure total problem for only month row context, so add a new measure as below:

Measure 3 = SUMX(VALUES(Table1[Actual Date]),[Measure 2])

See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Regards,

Lin

[Amardeep100115]

Hi @v-lili6-msft
Any scope of help on this

[Amardeep100115]

Hi @v-lili6-msft
Please
help on this

[Amardeep100115]

Hi


please have a look attached file i have entered my data in Sheet1 table.

i have a measure Avg Wkly and Daily occu

Could you please help me with same results in Table one ? with Avg Weekly column and 90% persentile (90 Days) as well)

[v-lili6-msft]

hi  @Amardeep100115 

Do you mean Avg Weekly is a measure? I couldn't see Daily occu column too.

From the excel file, you must have a column to create a measure,

it is better you could share your sample pbix file for us have a test. that will be a great help.

Regards,

Lin

[v-lili6-msft]

hi @Amardeep100115 

I don't see any expected output in this excel file:

Please share your expected output in excel, that way, we will give you further help in power bi.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Amardeep100115]

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]

hi @Amardeep100115 

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

[Amardeep100115]

Is it possible to help me with BI file ?

Please

[Greg_Deckler]

@Amardeep100115 - Perhaps this will help: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991