Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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