Return unrelated value to column header when no calculation can be performed

[Timetxu]

Hi everyone,

I'm running into an issue which feels like an easy fix but one I've been unnecessarily wasting time on:

I'm creating a sales forecasting visual. In here, you see the cumulative sales values of the previous three years as well as the cumulative sales values of the current year, up until the last completed month. What I've done is I've added a factor in there to project how much the total sales for the current year might end up like. The factor is the average increase of the YTD totals of the previous three years. The projection is then found by simply multiplying that average factor with the current YTD sales.

Obviously, this formula returns blanks in months that haven't happened yet, but to create a visual with a trend line, I would like all blank cells to return the most recent projection, ie I want every cell from March to December to return €540,126.02.

Many thanks for the help!

[v-zhouwen-msft]

Hi @Greg_Deckler ,thanks for the quick reply, I'll add more.

Hi @Timetxu ,

Regarding your question, I have assumed some data.

1.I simplified the calculation of 'YTD' and 'AvgFactory' but made sure they were measures.

Use the following DAX expression to create a measure named ‘AvgFactory_’

AvgFactory_ = SUM('Table'[AvgFactory]) 

Use the following DAX expression to create a measure named ‘YTD’

YTD = SUM('Table2'[Amount])

Use the following DAX expression to create a measure named ‘Projection’

Projection = 
VAR _a = [YTD] * [AvgFactory_]
VAR _b = IF(ISBLANK([YTD]),
CALCULATE(SUMX(FILTER('Table2','Table2'[Month] = MONTH(TODAY())),'Table2'[Amount]),ALL('Table')) * CALCULATE(SUMX(FILTER('Table','Table'[Month] = MONTH(TODAY())),'Table'[AvgFactory]),ALL('Table'))  ,
_a)
RETURN _b

2.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Timetxu Assuming that Projection is a column you could do this:

Measure = 
  VAR __Year = MAX('Table'[Year])
  VAR __Projection = MAX('Table'[Projection])
  VAR __LastProjectionMonth = MAXX(FILTER(ALL('Table'), [Year] = __Year && [Projection] <> BLANK()), [Month])
  VAR __LastProjection = MAXX(FILTER(ALL('Table'), [Year] = __Year && [Month] = __LastProjectMonth), [Projection])
  VAR __Result = IF( __Projection = BLANK(), __LastProjection, __Projection)
RETURN
  __Result

If Projection is a measure:

Measure = 
  VAR __Year = MAX('Table'[Year])
  VAR __Projection = [Projection]
  VAR __Table = 
    SUMMARIZE(
      'Table'
      [Year],
      [Month],
      "__Projection", [Projection]
    )
  VAR __LastProjectMonth = MAXX(FILTER(__Table), [Year] = __Year && [Projection] <> BLANK()), [Month])
  VAR __LastProjection = MAXX(FILTER(__Table), [Year] = __Year && [Month] = __LastProjectMonth), [Projection])
  VAR __Result = IF( __Projection = BLANK(), __LastProjection, __Projection)
RETURN
  __Result

[Timetxu]

@Greg_Deckler 

Thanks for your quick reply!

Unfortunately it didn't change the outcome though I feel like we're pretty close here. I tried to use an IF statement as well, but I think it doesn't work because the trend is based off of the projection measure, which in turn refers to the YTD measure. The Projection measure is simply YTD * AvgFactor with the YTD formula being:

YTD =

IF(

    MAX('Date'[Date]) <= TODAY(),

CALCULATE(

    TOTALYTD(

        SUM('Sales'[Transaction]),

        'Date'[Date],

        'Date'[Month],

        "31/12"

        ),

        'Date'[Year] = YEAR(TODAY())),BLANK())