Forum Discussion

oscarII's avatar
oscarII
New Member
10 years ago
Solved

cumulative for certain period

We are creating a measure to cumulate Sales figures, so we can see cumulated sales for the last month/year etc, or any filtered period. The table we are summing contains a line for each invoice line ...
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    oscarII - OK played with this a bit, tested some things out and fixed a column format issue on my end. Here is what I came up with that I believe will get you what you want:

     

    Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED(BI-Dates),
        'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
    )
)

    The ALLSELECTED restricts the table returned to only the time range selected. I was able to put BI-Dates[Date] and Cumulative 2 in a table and watch the running total add up each day. I also used a [Month] column from BI-Dates and was able to get different (correct) cumulative totals for January versus February.

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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
Not applicable
8 years ago

Any one know why it does this?

 

I've selected previous 12 calendar months.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Expanding on ChristianH comments, I suggest using Blank() instead of 0

     

     

    Cummulative By Year = IF(max(Dates[Date])>Dates[Current Date], BLANK(), CALCULATE(SUM('Table'[Field]), DATESYTD(Dates[Date])))