Forum Discussion
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
- Anonymous8 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
- AnonymousNot 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- AnonymousNot applicable
Thanks Lydia!
I took out one of the SUMs and it worked a treat!