Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Running Total with Snapshot Data

Hi,

 

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

 

DataAsAtDateFinancialYearActualForecastCurrentReportingFY
1/06/20171/07/20152015/20167256422015/2016
1/06/20171/08/20152015/20165034502015/2016
1/06/20171/09/20152015/20168067962015/2016
1/06/20171/10/20152015/201698810462015/2016
1/06/20171/11/20152015/20163853542015/2016
1/06/20171/12/20152015/20168798642015/2016
1/06/20171/01/20162015/20167967682015/2016
1/06/20171/02/20162015/20165766462015/2016
1/06/20171/03/20162015/20164123122015/2016
1/06/20171/04/20162015/20161681542015/2016
1/06/20171/05/20162015/20166196192015/2016
1/06/20171/06/20162015/20162973262015/2016
1/06/20171/07/20162016/2017 4992015/2016
1/06/20171/08/20162016/2017 1212015/2016
1/06/20171/09/20162016/2017 3902015/2016
1/06/20171/10/20162016/2017 5782015/2016
1/06/20171/11/20162016/2017 4482015/2016
1/06/20171/12/20162016/2017 8792015/2016
1/06/20171/01/20172016/2017 2562015/2016
1/06/20171/02/20172016/2017 3042015/2016
1/06/20171/03/20172016/2017 2432015/2016
1/06/20171/04/20172016/2017 5352015/2016
1/06/20171/05/20172016/2017 2372015/2016
1/06/20171/06/20172016/2017 9912015/2016
1/07/20171/07/20152015/20167256422016/2017
1/07/20171/08/20152015/20165034502016/2017
1/07/20171/09/20152015/20168067962016/2017
1/07/20171/10/20152015/201698810462016/2017
1/07/20171/11/20152015/20163853542016/2017
1/07/20171/12/20152015/20168798642016/2017
1/07/20171/01/20162015/20167967682016/2017
1/07/20171/02/20162015/20165766462016/2017
1/07/20171/03/20162015/20164123122016/2017
1/07/20171/04/20162015/20161681542016/2017
1/07/20171/05/20162015/20166196192016/2017
1/07/20171/06/20162015/20162973262016/2017
1/07/20171/07/20162016/20174004992016/2017
1/07/20171/08/20162016/2017 1462016/2017
1/07/20171/09/20162016/2017 3902016/2017
1/07/20171/10/20162016/2017 5692016/2017
1/07/20171/11/20162016/2017 4332016/2017
1/07/20171/12/20162016/2017 8732016/2017
1/07/20171/01/20172016/2017 2352016/2017
1/07/20171/02/20172016/2017 3162016/2017
1/07/20171/03/20172016/2017 2702016/2017
1/07/20171/04/20172016/2017 5222016/2017
1/07/20171/05/20172016/2017 2482016/2017
1/07/20171/06/20172016/2017 9642016/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]
	)
)
DataAsAtFinancialYearDateActualYTDActualsCurrentYTDActualsCurrentYTDActuals (desired result)
1/06/20172015/20161/07/2015725725725725
  1/08/201550312285031228
  1/09/201580620348062034
  1/10/201598830229883022
  1/11/201538534073853407
  1/12/201587942868794286
  1/01/201679650827965082
  1/02/201657656585765658
  1/03/201641260704126070
  1/04/201616862381686238
  1/05/201661968576196857
  1/06/201629771542977154
 2016/20171/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/20172015/20161/07/2015725725  
  1/08/20155031228  
  1/09/20158062034  
  1/10/20159883022  
  1/11/20153853407  
  1/12/20158794286  
  1/01/20167965082  
  1/02/20165765658  
  1/03/20164126070  
  1/04/20161686238  
  1/05/20166196857  
  1/06/20162977154  
 2016/20171/07/2016400400400400
  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    
Need Help

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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