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
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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors