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.

 

  • Maxime's avatar
    Maxime
    Icon for Advocate I rankAdvocate I
    9 years ago

     

    You should rather compare the Maximum's date to today's date like this: 

     

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

     

    • brianhackett5's avatar
      brianhackett5
      Icon for Helper I rankHelper I
      9 years ago

       

      Hi there, 

       

      I need to have the cumulative kilometres from another table showing on this table. The cumulative kilometres would need correspond with the dates on the above table.

      Is this possible?

  • oscarII's avatar
    oscarII
    New Member
    10 years ago

    Greg_Deckler... Just one more point on this - the cumulative amount runs past the end of the available data and you end up with a graph looking like this:

     

     

     

     

     

     

     

     

     

     

    Is there any way that the formula can be amended to only show where there is data against that particular date?

     

    Thanks for the help.

    • greggyb's avatar
      greggyb
      Icon for Resident Rockstar rankResident Rockstar
      10 years ago
      Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED(BI-Dates),
        'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
            && 'BI-Dates'[Date] <= TODAY()
    )
)

      Just remove all dates greater than the system date from the date dimension. && is the logical and operator.

      • aruntd77's avatar
        aruntd77
        Icon for Helper I rankHelper I
        10 years ago

        Hi,

         

        I tried your approach to eliminate the future dates, however it is not filtering.

         

        Cumulative Actual Units = CALCULATE(SUM('DWH W_ORDER_HEADER_D'[Sold Count]), FILTER(ALLSELECTED('DWH W_DAY_D'), 'DWH W_DAY_D'[CALENDAR_DATE] <= MAX('DWH W_DAY_D'[CALENDAR_DATE]) && 'DWH W_DAY_D'[CALENDAR_DATE] <= TODAY()))

         

        Please help

         

         

        Thanks

        Arun

  • oscarII's avatar
    oscarII
    New Member
    10 years ago

    Greg_Deckler - Thanks very much, that's working perfectly now :smileyhappy:

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