cancel
Showing results for
Did you mean:  Helper I

## Calculate an Estimated YTD Total with a static Measure

Considering I have the following table:

 Date Sales YTD Sales Static Estimate for Sales Jan-2021 1000 1000 500 Feb-2021 2000 3000 500 Mar-2021 1000 4000 500 Apr-2021 1500 5500 500 May-2021 1500 7000 500 Jun-2021 7500 500 Jul-2021 8000 500 Aug-2021 8500 500 Sep-2021 9000 500

How do I go about creating a measure or calculated column for the YTD Sales column? The formula is YTD, but if there is no value for the current month, then add the Static Estimate value instead (to show something like a forecast).

1 ACCEPTED SOLUTION  Community Support

Hi, @lancea ;

You could try to delete the .date in the picture, if it doesn’t work, you can just copy my code and change the key field The final output is shown below: Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4  Community Support

Hi, @lancea ;

You could create a measure by the following formula:

``````YTD Sales1 =
VAR _sale =
CALCULATE ( SUM ( [Sales] ), DATESYTD ( 'Table'[Date], "12/31" ) )
VAR _static =
CALCULATE (
SUM ( [Static Estimate for Sales] ),
DATESYTD ( 'Table'[Date], "12/31" ),
FILTER ( ALL ( 'Table' ), [Sales] = BLANK () )
)
RETURN
IF ( MAX ( [Sales] ) = BLANK (), _static + _sale, _sale )
``````

Or you could create a column:

``````YTD Sales2 =
VAR _sales =
CALCULATE (
SUM ( [Sales] ),
DATESYTD ( 'Table'[Date], "12/31" ),
ALL ( 'Table' )
)
VAR _static =
CALCULATE (
SUM ( [Static Estimate for Sales] ),
DATESYTD ( 'Table'[Date], "12/31" ),
FILTER ( ALL ( 'Table' ), [Sales] = BLANK () )
)
RETURN
IF ( [Sales] = BLANK (), _sales + _static, _sales )
``````

The final output is shown below: Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Helper I

Hello,

Unfortunately, I am getting the incorrect result as shown below:

YTD Sales1 =
VAR _sale =
CALCULATE ( SUM ( [Sales] ), DATESYTD ( Sheet1[Date Description].[Date], "12/31" ) )
VAR _static =
CALCULATE (
SUM ( Sheet1[static] ),
DATESYTD ( Sheet1[Date Description].[Date], "12/31" ),
FILTER ( ALL ( Sheet1 ), [Sales] = BLANK () )
)
RETURN
IF ( SUM ( [Sales] ) = 0, _static + _sale, _sale ) Please note that static column in my case is a CalculatedColumn with a flat value of 75 (static = 75). And here is my dataset (for some reason, it won't allow me to post table): Can you please let me know what I am doing wrong?  Community Support

Hi, @lancea ;

You could try to delete the .date in the picture, if it doesn’t work, you can just copy my code and change the key field The final output is shown below: Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Super User IV

@lancea , Try a measure like this with help from date table

YTD Sales = CALCULATE(SUMX(values(Date[Month Year]) , calculate(if isblank(sum(Sales[Sales])) , sum(Table[Static Estimate]), sum(sales[Sales]))),DATESYTD('Date'[Date],"12/31"))

Proud to be a Super User! Announcements #### Welcome to the User Group Public Preview  