Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
User | Count |
---|---|
131 | |
74 | |
70 | |
58 | |
53 |
User | Count |
---|---|
190 | |
97 | |
67 | |
62 | |
54 |