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.

     

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
9 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

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

    Hi Willborn,

     

    I know I am replying this very late. Yes we had fixed it by adding one additional column in Date Dimension Table called "TillDate". We have this table in SQL DB, and get updated on daily basis. The Till Date column will propulate all the dates till today, any records beyond today's date will be set to blank. We also have Calender Date column which is populated for all dates in Date dimension table.

     

    So, for actuals till today, we are using below formula. Please note on <= MAX(datedim[TillDate]) clause.

     

    Sold Cumulative = CALCULATE(Calculate(Sum(FactsActuals[OrderNetsellingPrice]),FactsActuals[Custom_Order_Status]="SOLD",FactsActuals[UnitType]<>"RETAIL")-Calculate(Sum(FactsActuals[OrderNetsellingPrice]),FactsActuals[Custom_Order_Status]="Cancelled",FactsActuals[StatusBeforeCancellation]="Sold",FactsActuals[UnitType]<>"RETAIL"),filter(ALLSELECTED(DateDim),DateDim[Calendar_date]<=max(DateDim[Tilldate])))

     

    The above will be restricted because remaining dates for the year after today is set to BLANK OR NULL, so the chart will not consider blank values.

     

    We use Target Sales formula as below. In this case, it uses Filter Context, where in I would have selected year as 2017, so it uses only the dates that are in the filter context and since I am considering Calendar_date column which has dates for full year. So, you will see cumulative numbers for the full year in this case.

     

    Target Sales Cumulative = CALCULATE(sum(FactTarget[TargetValue]),filter(ALLSELECTED(DateDim),DateDim[Calendar_date]<=max(DateDim[Calendar_date])),FactTarget[ACTIVE_FLG]="Y")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 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.