Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

How to ignore unreported quarter in Year-to-date calculation?

Hi all,


I'm creating a year-to-date line graph of values that get updated every quarter. I'm trying to find a way to not show quarters that haven't reported Actual values yet in the visual. Assume we are in Q3, I want to show Actual values up to (including) Q3 (So Q1, Q2, Q3) but need to have the line graph stop at Q3 and not go forward. 

To complicate things, I have other values (Target and Forecast) that are indeed shown for the full-year (meaning for all quarters regardless of what quarter we are in). To summarize, I want the line chart to:

- Show Actual only till the point that we have actuals in the data
- Show Target and Forecast for the full-year regardless of how many Actual values we have. 

Of course for the previous years we will always have All three values for all the quarters since they have passed and past quarters will always have Actual values. 

Important to remember
- Also, I'm able to do exactly this using non-YTD values (Total values in other words) and PBI engine easily picks up that I have no total value for Actual after Q3 and stops there while continuing to show the lines for Target and Forecast. I'm looking to achieve the same using YTD formula.

- I have one table containing all the values and a column (called "Version") in the same table labelling each value as "Actual" "Forecast" and "Target". The same table also has a column called "Reporting Date" that specifies the period for which that value is applicable for.

rushi_0-1658718242699.pngThis is an example showing how I want the Actual line to end at Q3 (or whatever the last reported quarter is) and how the other lines should continue regardless. 

Here is the formula I am using to calculate the Total Values 

Total Values = 
SUM ( 'Inventory Data'[Value] ),

And here is the formula I use to calculate YTD values
Values YTD =
Metrics[Total Values],
'Calendar Table'[Date],

I also have a measure to calculate the date that the last Actual was reported if anyone finds it useful
LastActualDate =
VAR MAXActualDate =
MAX ( 'Inventory Data'[Reporting Date] ),
FILTER(ALL('Inventory Data'),
[Version] = "Actual"

Super User
Super User

@rushi , Try a measure like


Values YTD =
var _max =MAXX(allselected('Inventory Data') , 'Inventory Data'[Reporting Date] )
if( 'Calendar Table'[Date] <=_max ,
Metrics[Total Values],
'Calendar Table'[Date],
), blank())

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors