Cumulative for Filtered Period

[Anonymous]

Hi All

I've got the issue where I want my data visuals to be dynamic to the dates but for some reason it's still cumulating data from the previous dates.

For example, I have a data set that spreads from 01/01/2016 to the current date. I want to be able to use a drop down menu to select which year to display a chart Jan - Dec.

When I select 2016, it works fine...because I don't have any data from 2015. But when I select 2017, it takes data from 2016 and keeps cumulating into Jan. Anyone know how I can start cumulating only for the date range I've selected?

Note, this is my formula:

Cumulative Spend = CALCULATE (
    SUM ( [Amount (Excl VAT)] ),
    FILTER (
        ALLSELECTED('Vendor Ledger'[Document Date].[Date]),
        'Vendor Ledger'[Document Date].[Date] <= MAX ( 'Vendor Ledger'[Document Date].[Date] )
     && 'Vendor Ledger'[Document Date].[Date] <= TODAY()  )
)

Thank you

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create your measure using dax below.

Cumulative Spend =TOTALYTD(SUM(SUM ( [Amount (Excl VAT)] ), 'Vendor Ledger'[Document Date].[Date], 'Vendor Ledger'[Document Date].[Date]<=TODAY())



Regards,
Lydia

[Anonymous]

Thanks Lydia!

I took out one of the SUMs and it worked a treat!