cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gio1082
Helper I
Helper I

Measurement for active days open over fiscal month

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

gio1082_0-1623969454032.png

 


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
v-xiaotang
Community Support
Community Support

Hi @gio1082 

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

vxiaotang_0-1624260024346.png

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.

The table in my first post is what I want to create. What I have is this: 
gio1082_0-1624277764364.png
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. 
gio1082_1-1624278439447.png

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

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors