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.

     

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

    • Willborn's avatar
      Willborn
      Icon for Advocate III rankAdvocate III
      10 years ago

      Hi Arun

       

      I struggle with the same issue. The expression <= TODAY() is somehow not working.

       

      Did you solved this already? If yes - how?

       

      Regards Patrick

      • ChristianH's avatar
        ChristianH
        Frequent Visitor
        9 years ago

        Hi,

         

        I ran in to the same problem and fixed it with a simple IF-statement, which sets the measure to zero if the MAX of date is higher than TODAY():

        Cumulative no of leads = IF(max(Dates[Dates])>today(),0,CALCULATE(COUNTA('lead'[fullname]),Filter(ALLSELECTED(Dates),Dates[Date]<=max(Dates[Date])


        Regards,
        Christian

  • oscarII's avatar
    oscarII
    New Member
    10 years ago

    Many thanks greggyb, we needed to make some adjustments to suit our dataset but have managed to resolve this now!