Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Cumulative for Filtered Period

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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      Anonymous
      Not applicable

      Thanks Lydia!

       

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