Cumulative SUM when measure contains percentage

[Dave_Gugg]

Good morning

I am working in a retail sales environment.  I have been tasked with creating a visual which takes the previous year's product returns % (products returned/products sold) and multiplying that by current year's projected sales to get a projected return $.  I am able to get this correctly when looking at the total projected returns each week, but when I attempt to make the projected returns cumulative, I am unable to get the correct results.  I believe what is happening is the projected returns % (equal to previous year's return % for all future dates) is being made cumulative, which is driving it close to 0, so my cumulative projected return $ is not as high as it should be.  I am using two helper tables that only have a season name in it to dynamically bring in the correct dates for the sales season.  They are called 'Current Season' and 'Previous Season'  Here are my relevant measures:

Merch Budget = CALCULATE(SUM('Merch Budget'[Demand Budget Dollars]),'Merch Budget'[Merch Budget Standard Season] = "Spring 2017")
Current Season Demand $ = CALCULATE(SUM(Demand[Ship $]),intersect(values('Ship Date'[Order Date Season]),values('Current Season'[Current Season])))
Current Season Return $ = CALCULATE(SUM(Demand[Return $]),intersect(values('Ship Date'[Order Date Season]),values('Current Season'[Current Season])),INTERSECT(values('Original Order Date'[Original Order Date Season]),values('Current Season'[Current Season])),FILTER(ALL(Demand),Demand[Ship Date] <= max(Demand[Ship Date])))
Cumulative Projected Return $ = if(ISBLANK(Demand[Current Season Demand $]),CALCULATE(Demand[Projected Return $] + Demand[Current Season Return $ Total],FILTER(all(Demand),Demand[Ship Date Week of Year] <= max(Demand[Ship Date Week of Year]))),blank())
Projected Return $ = if(Demand[Current Season Return $]<> BLANK(),BLANK(),[Merch Budget]*[Projected Return %])
Projected Return % = IF([Current Season Return $]>0,blank(),if([Previous Season Return %] - [Current Season Return %]<=0,blank(),[Previous Season Return %] - [Current Season Return %]))
Previous Season Return % = DIVIDE([Previous Season Return Count],[Previous Season Demand Count],0)
Previous Season Return Count = CALCULATE(sum(Demand[Return Units]),intersect(values('Ship Date'[Order Date Season]),values('Previous Season'[Previous Season])),INTERSECT(values('Original Order Date'[Original Order Date Season]),values('Previous Season'[Previous Season])))
Previous Season Demand Count = CALCULATE(SUM(Demand[Ship U]),intersect(values('Ship Date'[Order Date Season]),values( 'Previous Season'[Previous Season])))

Here is how it is looking now:

You can see I have successfully gotten the Project Return $, but when I try to make it cumulative it just goes to the flat amount equal to the current season return $.

How can I create this rolling sum when the measure beneath it is based on a percentage?

Thank you

[v-caliao-msft]

@Dave_Gugg,

You can calculate cumulative total by using DAX easily. The sample expression looks like


Cumulative Quantity :=

CALCULATE (

    SUM ( Transactions[Quantity] ),

    FILTER (

        ALL ( 'Date'[Date] ),

        'Date'[Date] <= MAX ( 'Date'[Date] )

    )

)

In your scenario, could try to achieve it by using DAX, here is a blog for you reference.

http://www.daxpatterns.com/cumulative-total/

Regards,

Chalrie Liao

[Dave_Gugg]

@v-caliao-msft, thanks for the reply, but I don't think I explained my problem well enough.  I have calculated a projected Return $ by taking a sales budget (measure from data source) and multiplying it by the previous season return percentage, as calculated by previous season units returned divided by previous season units sold.  This works well to give me a projection of what will be returned each week.  See highlighted line below:

I want to create another line that makes this measure cumulative.  However, since it is based on the previous season return percentage, if I use the pattern you showed above, the percentage becomes cumulative, and the calculation is incorrect.  To see that, I can look at the value for Project Return $ 5, which uses the pattern you show, for weeks 22 and 23.  Although the highlight line goes from $7,757 in week 22 to $5,061 in week 23, the cumulative line actually goes down from $160,044 in week 22 to $157,509 in week 23.  This happens because the cumulative return percentage drops faster than the Merch Budget increases.  Having a cumulative projected return $ that goes down one week to the next makes no sense.  Please see below for screenshots of the numbers I showed:

Does that make sense?

[Dave_Gugg]

This question hasn't received a good answer, so I'll try to use a table to explain what I'm trying to find.

All of the columns in this table are working as expected except Cumulative Projected Return $ 5, the column to the far right above.

The columns are as follows:

• Order Week of Year: This comes straight from my date dimension table
• Merch Budget: This comes from a budget fact table.  It is a sum of daily budget numbers aggregated by week.
• Previous Season Demand Count: This is a sum of the count of units sold from the previous season.  I use a helper table to identify the previous season.  The DAX formula is CALCULATE(SUM(Demand[Ship U]),intersect(values('Ship Date'[Order Date Season]),values( 'Previous Season'[Previous Season])))
• Previous Season Return Count: This is the sum of the count of units returned from the previous season.  It also uses a helper table to identify the previoyus season.  The formula is CALCULATE(sum(Demand[Return Units]),intersect(values('Ship Date'[Order Date Season]),values('Previous Season'[Previous Season])),INTERSECT(values('Original Order Date'[Original Order Date Season]),values('Previous Season'[Previous Season])))
• Projected Return %: This is just the return % for the previous season, as long as the week of year is in the past.  The DAX formula is IF([Current Season Return $]>0,blank(),if([Previous Season Return %] - [Current Season Return %]<=0,blank(),[Previous Season Return %] - [Current Season Return %]))
• Cumulative Projected Return % : Not important for this discussion.
• Projected Return $: This is basically the Merch Budget multiplied with the Projected Return %.  This correctly gives me the Projected Return $ on a weekly basis.  The DAX formula is if(Demand[Current Season Return $]<> BLANK(),BLANK(),[Merch Budget]*[Projected Return %]).
• Cumulative Projected Return $ 5: This is the measure I am having trouble creating.  I want it to be the cumulative sum of the previous column, Projected Return $.  So for week 29 it will be $16,068.49.  For week 30 it will be $25,549.86, and for week 31 it should be $27,118.44.  Using the cumulative sum DAX pattern my formula is this, but it is not giving any results: CALCULATE([Projected Return %] * [Merch Budget],FILTER(all('Ship Date'),'Ship Date'[Order Week of Year] <= max('Ship Date'[Order Week of Year])))

Can anyone help me figure out how to create this cumulative sum?