Measurement for active days open over fiscal month

[gio1082]

Hi I am trying to create this table for active subscribers using measurements , 

Where P90= PERCENTILE.INC(Sheet1[Active dates open],.9)

Here is my dashboard
https://www.dropbox.com/s/rgda0irrt7pn88o/Active.pbix?dl=0

In one sheet have a original submission date, closed date (if null, it means its still open), active dates open, and tier. In the other sheet I have calendar dates by fiscal month. I completed the first two columns but I need help on the rest. 

[v-xiaotang]

Hi @gio1082 

I've downloaded your PBI file but I didn't find the table in your capture,

and do you mean you need to calculate the last 4 columns ([Average of ...], [P90 overall for...], [P90 tier 2 for ...], [P90 tier 3 for...] ) with measure? however, we are not sure how you calculate the 4 columns, please share the Mathematical formula so that we can work on it further.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[gio1082]

The table in my first post is what I want to create. What I have is this: 

I am using 2 measuments in this table. 
Measurement 1 (coumn 2):

----------------------------------

Total No of Active =

var currentdate = MAX('Calendar'[Calendar Date])

var Active =

CALCULATE(

COUNTROWS(Sheet1),

All('Calendar'),

'Calendar'[Calendar Date]<=currentdate,

ISBLANK(Sheet1[Close Date])

|| Sheet1[Close Date]>=currentdate

)

Var Result=

IF(

YEAR(currentdate)<=YEAR(TODAY()),

Active

)

RETURN

Result
------------------------------------------

mesuament 2 (column 3) 

Total active greater 1 year =

VAR currentDate = MAX('Calendar'[Calendar Date])

VAR firstHireDate = MIN(Sheet1[Original Submission Date])

VAR active =

CALCULATE(

COUNTROWS(Sheet1),

ALL('Calendar'),

Sheet1[Original Submission Date]<=currentDate,

Sheet1[Close Date]>currentDate

|| ISBLANK(Sheet1[Close Date]),

DATEDIFF(Sheet1[Original Submission Date], currentDate, MONTH) >= 12

)

VAR result=

IF(

YEAR(currentDate)<=YEAR(TODAY())

&& currentDate>=firstHireDate,

active

)

Return

result

-----------------------------------

Here is a vidio I used to create these two measurements:
https://www.youtube.com/watch?v=c-mLYMWTii4&t=177s

Column 2 and 3 are completed, Now a measurment for column 4,5,6, and 7 which is what I asking help for. 

I assume you need to edit my firsts two measurments to get the you need to calculate the last 4 columns ([Average of ...], [P90 overall for...], [P90 tier 2 for ...], [P90 tier 3 for...] )
  for average of... column its getting the average of my 3 column.
For (P90 overall for..) adding PERCENTILE.INC(Sheet1[Active dates open],.9) to my first column 
For [P90 tier 2 for ...], adding PERCENTILE.INC(Sheet1[Active dates open],.9) to my first column where tier =2