Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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.

 

incident rate.png

 

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.

 

incident rate no zero.png

 

What I want is the first graph, but the line to stop at the current month.

1 ACCEPTED SOLUTION
amitchandak
Super User
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))

 

 

ShowItemwithoutdata.JPG

View solution in original post

3 REPLIES 3
amitchandak
Super User
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))

 

 

ShowItemwithoutdata.JPG

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.

incident table.png

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! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.