Running Total with Snapshot Data

[Anonymous]

Hi,

I have a fact table with monthly snapshots of data. The format is as follows:

DataAsAt	Date	FinancialYear	Actual	Forecast	CurrentReportingFY
1/06/2017	1/07/2015	2015/2016	725	642	2015/2016
1/06/2017	1/08/2015	2015/2016	503	450	2015/2016
1/06/2017	1/09/2015	2015/2016	806	796	2015/2016
1/06/2017	1/10/2015	2015/2016	988	1046	2015/2016
1/06/2017	1/11/2015	2015/2016	385	354	2015/2016
1/06/2017	1/12/2015	2015/2016	879	864	2015/2016
1/06/2017	1/01/2016	2015/2016	796	768	2015/2016
1/06/2017	1/02/2016	2015/2016	576	646	2015/2016
1/06/2017	1/03/2016	2015/2016	412	312	2015/2016
1/06/2017	1/04/2016	2015/2016	168	154	2015/2016
1/06/2017	1/05/2016	2015/2016	619	619	2015/2016
1/06/2017	1/06/2016	2015/2016	297	326	2015/2016
1/06/2017	1/07/2016	2016/2017		499	2015/2016
1/06/2017	1/08/2016	2016/2017		121	2015/2016
1/06/2017	1/09/2016	2016/2017		390	2015/2016
1/06/2017	1/10/2016	2016/2017		578	2015/2016
1/06/2017	1/11/2016	2016/2017		448	2015/2016
1/06/2017	1/12/2016	2016/2017		879	2015/2016
1/06/2017	1/01/2017	2016/2017		256	2015/2016
1/06/2017	1/02/2017	2016/2017		304	2015/2016
1/06/2017	1/03/2017	2016/2017		243	2015/2016
1/06/2017	1/04/2017	2016/2017		535	2015/2016
1/06/2017	1/05/2017	2016/2017		237	2015/2016
1/06/2017	1/06/2017	2016/2017		991	2015/2016
1/07/2017	1/07/2015	2015/2016	725	642	2016/2017
1/07/2017	1/08/2015	2015/2016	503	450	2016/2017
1/07/2017	1/09/2015	2015/2016	806	796	2016/2017
1/07/2017	1/10/2015	2015/2016	988	1046	2016/2017
1/07/2017	1/11/2015	2015/2016	385	354	2016/2017
1/07/2017	1/12/2015	2015/2016	879	864	2016/2017
1/07/2017	1/01/2016	2015/2016	796	768	2016/2017
1/07/2017	1/02/2016	2015/2016	576	646	2016/2017
1/07/2017	1/03/2016	2015/2016	412	312	2016/2017
1/07/2017	1/04/2016	2015/2016	168	154	2016/2017
1/07/2017	1/05/2016	2015/2016	619	619	2016/2017
1/07/2017	1/06/2016	2015/2016	297	326	2016/2017
1/07/2017	1/07/2016	2016/2017	400	499	2016/2017
1/07/2017	1/08/2016	2016/2017		146	2016/2017
1/07/2017	1/09/2016	2016/2017		390	2016/2017
1/07/2017	1/10/2016	2016/2017		569	2016/2017
1/07/2017	1/11/2016	2016/2017		433	2016/2017
1/07/2017	1/12/2016	2016/2017		873	2016/2017
1/07/2017	1/01/2017	2016/2017		235	2016/2017
1/07/2017	1/02/2017	2016/2017		316	2016/2017
1/07/2017	1/03/2017	2016/2017		270	2016/2017
1/07/2017	1/04/2017	2016/2017		522	2016/2017
1/07/2017	1/05/2017	2016/2017		248	2016/2017
1/07/2017	1/06/2017	2016/2017		964	2016/2017

I have created a YTD measure which works as expected

YTDActuals:=TOTALYTD([Sum of Actual],Table1[Date], ALL(Table1[Date]), "6/30")

I am now trying to create a second measure which only shows YTD actuals dependant on the CurrentReportingFY. I have tried to filtered down the YTDActuals measure using the following DAX but achieved the following result (show in a pivot). It seems the below is removing the cumulative for some reason. I have also included the desired result

CurrentYTDActuals:=
CALCULATE(
	[YTDActuals],
	FILTER(
		Table1,
		Table1[FinancialYear] = Table1[CurrentReportingFY]
	)
)

DataAsAt	FinancialYear	Date	Actual	YTDActuals	CurrentYTDActuals	CurrentYTDActuals (desired result)
1/06/2017	2015/2016	1/07/2015	725	725	725	725
		1/08/2015	503	1228	503	1228
		1/09/2015	806	2034	806	2034
		1/10/2015	988	3022	988	3022
		1/11/2015	385	3407	385	3407
		1/12/2015	879	4286	879	4286
		1/01/2016	796	5082	796	5082
		1/02/2016	576	5658	576	5658
		1/03/2016	412	6070	412	6070
		1/04/2016	168	6238	168	6238
		1/05/2016	619	6857	619	6857
		1/06/2016	297	7154	297	7154
	2016/2017	1/07/2016
		1/08/2016
		1/09/2016
		1/10/2016
		1/11/2016
		1/12/2016
		1/01/2017
		1/02/2017
		1/03/2017
		1/04/2017
		1/05/2017
		1/06/2017
1/07/2017	2015/2016	1/07/2015	725	725
		1/08/2015	503	1228
		1/09/2015	806	2034
		1/10/2015	988	3022
		1/11/2015	385	3407
		1/12/2015	879	4286
		1/01/2016	796	5082
		1/02/2016	576	5658
		1/03/2016	412	6070
		1/04/2016	168	6238
		1/05/2016	619	6857
		1/06/2016	297	7154
	2016/2017	1/07/2016	400	400	400	400
		1/08/2016
		1/09/2016
		1/10/2016
		1/11/2016
		1/12/2016
		1/01/2017
		1/02/2017
		1/03/2017
		1/04/2017
		1/05/2017
		1/06/2017

[v-piga-msft]

Hi @Anonymous,

You could have a try with the formula below.

CurrentYTDActuals =
CALCULATE (
    SUM ( Table1[Actual] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[FinancialYear] = 'Table1'[CurrentReportingFY]
            && 'Table1'[Date] <= MAX ( 'Table1'[Date] )
    )
)

Then you will get the output you want.

Bests Regards,

Cherry