Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Running total over snapshots

Hi

I have a fact table where each month a snapshot is taken from our production system. The result is a format which looks like the following when pivoted. I also have a ‘ReportingFinancialYearText’ field which tells me which financial year it is when the snapshot it taken. In the pivot below it is 2016/2017 when the 2017May snapshot was taken and is 2017/2018 when the 2017Sep snapshot was taken

 

I have created a YTDActuals measure which calculates the running total for the financial year. This works as expected.

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

I am now trying to create a currentYTDActuals measure which shows the YTDActuals measure but only for the relevant financial year as dictated by the ‘ReportingFinancialYearText’ field. I have tried to just filter down the YTD actuals measure however this seems to remove the running total element. I have included the desired result below.

 

CurrentYTDActuals:=
CALCULATE(
	[YTDActuals],
	FILTER(
		Fact_Financials,
		Fact_Financials[FiscalYearText] = Fact_Financials[ReportingFinancialYearText]
	)
)

 

Any ideas what I am doing wrong?

 

Many thanks!

 

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support

    Hi bludb1,

     

    There may be something wrong with the condition in filter and the condition below means value in two columns should be same in every row context:

    Fact_Financials[FiscalYearText] = Fact_Financials[ReportingFinancialYearText]

    And in your table you have given I haven't found any value in [ReportingFinancialYearText] column. In addtion, it seems like values in [desiredresult] column is same with YTDActuals. You may just use DAX like this pattern though I'm not quite sure what's your specific expected result.

    CurrentYTDActuals :=
    CALCULATE ( [YTDActuals] )

     

    Regards,

    Jimmy Tao