Time intelligence related query

[rajibmahmud]

Hi, I am working with a set of historical data along with forecast from where I need to calculate current year YTD Actual

The data table is similar to below table. I have Actual 2017 till Jun and rest is forecast. My requirement is to calculate YTD Jun actual for CY, PY and so on as well as Full year value using time intelligence. I am aware it requires to have date assigned, for that I marked Jan 2015 as 01/01/2015, Feb 2015 as 01/02/2015 and so on using Custom column. Can someone please help? 

Version	Year	Month	Value
Actual	2015	Jan	307
Actual	2015	Feb	195
Actual	2015	Mar	302
Actual	2015	Apr	788
Actual	2015	May	703
Actual	2015	Jun	800
Actual	2015	Jul	159
Actual	2015	Aug	409
Actual	2015	Sep	615
Actual	2015	Oct	571
Actual	2015	Nov	141
Actual	2015	Dec	716
Actual	2016	Jan	151
Actual	2016	Feb	580
Actual	2016	Mar	346
Actual	2016	Apr	398
Actual	2016	May	419
Actual	2016	Jun	639
Actual	2016	Jul	525
Actual	2016	Aug	699
Actual	2016	Sep	668
Actual	2016	Oct	769
Actual	2016	Nov	774
Actual	2016	Dec	428
Actual	2017	Jan	688
Actual	2017	Feb	701
Actual	2017	Mar	750
Actual	2017	Apr	699
Actual	2017	May	640
Actual	2017	Jun	251
Forecast	2017	Jul	197
Forecast	2017	Aug	133
Forecast	2017	Sep	660
Forecast	2017	Oct	325
Forecast	2017	Nov	771
Forecast	2017	Dec	427

[kcantor]

Time intellegence requires a seperate and complete date table. make sure you have one with a valid relationship to your fact table. The method you use to create the date in your fact table is fine as long as your date table has all dates and not just the beginning of the month.

I would probably create a SUM using Calculate for the YTD filtering by your keyword of Version and leaving out anything that is listed as Forecast.

Something like:

Value Sume = CALCULATE(SUM(Fact[Value]), [Version]="Actual")

Then using the date table roll that calculation into a YTD.