Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
Solved! Go to Solution.
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.
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.
Hello,
Unfortunately, I am getting the incorrect result as shown below:
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?
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.
@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"))
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |