cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## YTD Average with Blank Months

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.

1 ACCEPTED SOLUTION
Super User

@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))

3 REPLIES 3
Super User

@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
Not applicable

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/

Anonymous
Not applicable

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.