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.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
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.

 

dwolfe1013's avatar
dwolfe1013
Regular Visitor
9 years ago

Hi!  I am trying to use your solution and it does not seem to work for me.

 

I have tried both of the following expressions, and both return the same results.  The values returned are cumulative back to the start of the query, rather than cumulative for only the date range shown in the visual.

 

Any ideas why the addition of the allselected function does not appear to be affecting the returned totals?

 

Thank you!

 

cumulative total=
CALCULATE (
    SUM ( 'table'[column]),
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

 

-or-

 

cumulative total=
CALCULATE (
    SUM ( 'table'[column]),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)