YTD Average with Blank Months

[Anonymous]

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.

[amitchandak · Accepted 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]

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]

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!