Forcasting TotalYTD for remaining of year

[Anonymous]

Hi all, 

I'm trying to get a graph that shows the total yearly cost using TotalYTD measure  and then when the data stops, forcast the cost for the rest of the year. The data I'm using for this is actually Azure spend. 

At the moment I have the measure 

       Cost YTD = TOTALYTD(sum('Usage details'[Cost]),'Usage details'[Date].[Date])

However, when this is added to line graph and I add a forcast, the forcast starts from the end of the year, not when the data in my table finishes (eg July)

I'm sure this should be really easy, but I've looked at multiple forums and tried to follow other posts, but I can't get it to work. If I change the year end to be todays date, then the totals are wrong, because I only want the sum from 1/1/2020.

Any help much appreciated. I'm new to powerbi.

Cheers.

[Anonymous · Accepted solution]

Hi there.

First off, to be able to do what you want, you have to create a hybrid measure that will unify both, the real spend and the forecast. So, for some dates you'll want to show the real calculation and for some other the forecast.

Secondly, I'd advise strongly against using TOTALYTD and automatic date tables. I'm not going to explain why as it would take a whole chapter in a book. If you want to create easy and bug-free constructs... stay away from them, especially automatic date tables. Create your own.

[Anonymous]

I Don't See any solution here How it is marked Accepted.
Even I'm going through same problem.
understand the dates to be segregate for the actual and forcast sales but how to calculate the forcast sales number

[Anonymous]

OK, thanks @Anonymous . I'll try that.

[Greg_Deckler]

@Anonymous Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

All that said, you may find these helpful:

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Also this one:

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-TOTALMTD/td-p/1250110

[Anonymous]

Thanks Greg for the links. I don't think the answer was on the Before you post.  

This is a sample data I get with the current measure

Year	Quarter	Month	Day	Cost	Cost YTD
2020	Qtr 3	July	21	14.68461	3474.819
2020	Qtr 3	July	22	14.68461	3489.504
2020	Qtr 3	July	23	14.68461	3504.189
2020	Qtr 3	July	24	14.68461	3518.873
2020	Qtr 3	July	25	14.68461	3533.558
2020	Qtr 3	July	26	14.62132	3548.179
2020	Qtr 3	July	27	0.886349	3549.066
2020	Qtr 3	July	28		3549.066
2020	Qtr 3	July	29		3549.066
2020	Qtr 3	July	30		3549.066
2020	Qtr 3	July	31		3549.066
2020	Qtr 3	August	1		3549.066
2020	Qtr 3	August	2		3549.066
2020	Qtr 3	August	3		3549.066
2020	Qtr 3	August	4		3549.066
2020	Qtr 3	August	5		3549.066
2020	Qtr 3	August	6		3549.066
2020	Qtr 3	August	7		3549.066

What I would like is for the Cost YTD to stop when there is no cost for that date. Then be able to add a forcast for the dates that have no current cost until the end of the year.

Year	Quarter	Month	Day	Cost	Cost YTD	Forcast Value
2020	Qtr 3	July	21	14.68461	3474.819
2020	Qtr 3	July	22	14.68461	3489.504
2020	Qtr 3	July	23	14.68461	3504.189
2020	Qtr 3	July	24	14.68461	3518.873
2020	Qtr 3	July	25	14.68461	3533.558
2020	Qtr 3	July	26	14.62132	3548.179
2020	Qtr 3	July	27	0.886349	3549.066
2020	Qtr 3	July	28			3563.750179
2020	Qtr 3	July	29			3578.434791
2020	Qtr 3	July	30			3593.119403
2020	Qtr 3	July	31			3607.804015
2020	Qtr 3	August	1			3622.488627
2020	Qtr 3	August	2			3637.17324
2020	Qtr 3	August	3			3651.857852
2020	Qtr 3	August	4			3666.542464

Cheers