cancel
Showing results for
Did you mean:
Helper I

## Measurement for active days open over fiscal month

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.

2 REPLIES 2
Community Support

Hi @gio1082

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.

Helper I

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:

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.