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?
You can calculate cumulative total by using DAX easily. The sample expression looks like
Cumulative Quantity :=
SUM ( Transactions[Quantity] ),
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.
@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?
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:
Can anyone help me figure out how to create this cumulative sum?
Check out the November 2023 Power BI update to learn about new features.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.