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

rushi — Mon, 25 Jul 2022 03:14:55 GMT

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.

This 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 =
CALCULATE (

SUM ( 'Inventory Data'[Value] ),

)

And here is the formula I use to calculate YTD values

Values YTD =

TOTALYTD (

Metrics[Total Values],

'Calendar Table'[Date],

"06/30"

)

I also have a measure to calculate the date that the last Actual was reported if anyone finds it useful

LastActualDate =

VAR MAXActualDate =

CALCULATE(

MAX ( 'Inventory Data'[Reporting Date] ),

FILTER(ALL('Inventory Data'),

[Version] = "Actual"

)

RETURN

MAXActualDate

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

amitchandak — Mon, 25 Jul 2022 04:19:56 GMT

rushi , Try a measure like

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

topic How to ignore unreported quarter in Year-to-date calculation? in DAX Commands and Tips

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

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