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 

 

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's avatar
v-lili6-msft
Community Support
6 years ago

hi  Anonymous 

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