Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I am trying to calculate a YTD incident rate and then show that rate as a line. Additionally, in the chart I would like to be able to see the entire current year.
The problem I am running into is that there are months when there may be no incidents. If there are months with no incidents in the past, then it should be 0 for that month. So the current measures look like:
Total Incidents =
CALCULATE(
COUNTROWS(
'Incidents'
),
'Incidents'[Incident Type] = "Critical"
) + 0
Monthly Incident Rate =
AVERAGEX(
VALUES(
DateKey[Month & Year]
),
'Incidents'[Total Incidents]
)
YTD Incident Rate =
CALCULATE(
'Incidents'[Monthly Incident Rate],
FILTER(
ALL(DateKey),
DateKey[MonthOfYear] <= MAX( DateKey[MonthOfYear] ) && DateKey[Year] = MAX( DateKey[Year] )
)
)
However, when I plot this, I get the incident rate calculating values into the future assuming there are 0 incidents in future months. Here's the graph, where the bars represent the incidents and the line shows the incident rate.
Now if I remove the +0 from the measure above, then on months with no incidents, it will calculate the rate and hold the last value.
What I want is the first graph, but the line to stop at the current month.
Solved! Go to Solution.
@Anonymous , Use option show item with now data ,ad also force YTD to stop on today or max date
example
YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))
YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
@Anonymous , Use option show item with now data ,ad also force YTD to stop on today or max date
example
YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))
YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
This works for QTY but how would I implement this for a YTD average?
Here's what I have so far:
YTD Incidents Forced =
var _max = TODAY()
return
if(max(DateKey[Date]) <= _max,
CALCULATE(COUNTROWS('Incidents'),'Incidents'[Incident Type] = "Critical", DATESYTD('DateKey'[Date])),
BLANK())
Monthly Incident Rate =
AVERAGEX(
VALUES(
DateKey[Month & Year]
),
'Incidents'[YTD Incidents Forced]
)
YTD Incident Rate =
CALCULATE(
'Incidents'[Monthly Incident Rate],
FILTER(
ALL(DateKey),
DateKey[MonthOfYear] <= MAX( DateKey[MonthOfYear] ) && DateKey[Year] = MAX( DateKey[Year] )
)
)
It appears to work up to the last calculation where it starts calculating for months beyond the current time.
I got this calculation from here:
https://blog.enterprisedna.co/calculate-a-year-to-date-ytd-monthly-moving-average-in-power-bi/
I was able to make it work!
YTD Incidents Forced =
var _max = DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()))
return
if(max(DateKey[Date]) <= _max,
CALCULATE( 'Incidents'[Monthly Incident Rate], FILTER( ALL(DateKey), DateKey[MonthOfYear] <= MAX( DateKey[MonthOfYear]) && DateKey[Year] = MAX( DateKey[Year]))),
BLANK()
)
I used your code to implement a better CALCULATE call with an additional filter. I also made var _max have an additional month so it would calculate for the current month. Not sure why that was needed but it worked.
Thanks for your help!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |