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
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])))