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.

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors