Filtered Year To Date doesnt SUM

[Anonymous]

Hi All,

I have a really simple table with two columns

FiscalPeriodEndDate, MonthlyTotal 

01 Jan 2019, 100

28 Feb 2019, 500

30 Mar 2019, -200

29 Apr 2019, 200

What id like to do is create a measure that displays a running total. I used the quick measure feature to create a year to date measure which worked fine except it displays values into the future. So i tried to edit it and filter the results so that the future dates were not displayed.  Unfortunaly my efforts have resulted in the measure no longer adding up the running total but just displaying the value for each line. 

BalanceAmt YTD =

TOTALYTD(

        SUM('rep ETLCashInBank'[MonthlyTotal]),

        'rep ETLCashInBank'[FiscalPeriodEndDate].[Date],

FILTER('rep ETLCashInBank', 'rep ETLCashInBank'[FiscalPeriodEndDate].[Date] <= MAX('rep ETLCashInBank'[FiscalPeriodEndDate].[Date]))

    )

Any help greatfully received!

Andy

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Just adjust your formula as below:

BalanceAmt YTD = 
TOTALYTD (
    SUM ( 'rep ETLCashInBank'[MonthlyTotal] ),
    'rep ETLCashInBank'[FiscalPeriodEndDate],
    FILTER (
        ALLSELECTED ( 'rep ETLCashInBank' ),
        'rep ETLCashInBank'[FiscalPeriodEndDate]
            <= MAX ( 'rep ETLCashInBank'[FiscalPeriodEndDate] )
    )
)

Result:

Best Regards,

Lin