Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello,
My requirement is to pull three years of data from today, the ask is to display length of stay over this three years of data by month (YYYYMM) in a line chart. In addition, add MEDIAN length of stay as a dash line, the calculation for MEDIAN is only take median of the first 10 starting months of data as circled on 2nd table screenshot.
To do this, I created a RANK column below as shown on the above table:
Solved! Go to Solution.
Hi @Buidoistreet ,
Please create a measure as below to work on it instead of using a calculated column.
Measure =
VAR mindate =
CALCULATE ( MIN ( 'ERAS'[date] ), ALLSELECTED ( ERAS ) )
VAR maxdate =
EDATE ( mindate, 10 )
RETURN
CALCULATE (
AVERAGE ( ERAS[Length_of_Stay] ),
FILTER ( ALL ( ERAS ), 'ERAS'[date] >= mindate && 'ERAS'[date] < maxdate )
)
For more details, please check the pbix as attached.
Hi @Buidoistreet ,
Please create a measure as below to work on it instead of using a calculated column.
Measure =
VAR mindate =
CALCULATE ( MIN ( 'ERAS'[date] ), ALLSELECTED ( ERAS ) )
VAR maxdate =
EDATE ( mindate, 10 )
RETURN
CALCULATE (
AVERAGE ( ERAS[Length_of_Stay] ),
FILTER ( ALL ( ERAS ), 'ERAS'[date] >= mindate && 'ERAS'[date] < maxdate )
)
For more details, please check the pbix as attached.
That helps. Thanks so much!
Thanks atmitchandak for a quick reply!
I created a new rank as column and I've received this error "The expression refers to mutiple columns. Multiple columns cannot be converted to scalar value."
new Rank =
var _min = min(allselected(ERAS), ERAS[Rank])
return
ERAS[Rank]-_min +1
Please advice. Thanks!
Oh, there was a typo in MINX, it should be
new Rank =
var _min = minx(allselected(ERAS), ERAS[Rank])
return
ERAS[Rank]-_min +1
However, the new rank value still look same as the old rank. It doens't dynamically re-rank.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
76 | |
56 | |
38 | |
34 |
User | Count |
---|---|
99 | |
56 | |
51 | |
44 | |
40 |