Forum Discussion
Rolling SUM in DAX
Hello all,
I have a table called FullView where i have information in the following format, that list for each pair of Ingredient-CalendarWeek, the existing stock plus the projected inbounds and outbounds/Forecast.
CW_ProdID Prod ID Ingredient Description Ingredient Name Weight CW Stock Inbound Forecast Ordinal
| 40-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 40 | 3308 | 0 | 0 | -3 |
| 40-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 40 | 9718 | 0 | 0 | -3 |
| 41-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 41 | 3308 | 0 | 0 | -2 |
| 41-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 41 | 9718 | 0 | 0 | -2 |
| 42-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 42 | 7049 | 0 | 3835 | -1 |
| 42-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 42 | 3308 | 0 | 0 | -1 |
| 43-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 43 | 3083 | 0 | 639 | 0 |
| 43-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 43 | 7049 | 0 | 0 | 0 |
| 44-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 44 | 583 | 0 | 0 | 1 |
| 44-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 44 | 7049 | 0 | 0 | 1 |
| 45-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 45 | 7049 | 0 | 5422 | 2 |
| 45-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 45 | 583 | 0 | 546 | 2 |
| 46-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 46 | 583 | 0 | 4518 | 3 |
| 46-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 46 | 7049 | 0 | 0 | 3 |
| 47-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 47 | 583 | 0 | 0 | 4 |
| 47-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 47 | 7049 | 0 | 0 | 4 |
| 48-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 48 | 583 | 0 | 0 | 5 |
| 48-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 48 | 7049 | 0 | 0 | 5 |
| 49-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 49 | 583 | 0 | 0 | 6 |
| 49-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 49 | 7049 | 0 | 0 | 6 |
| 50-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 50 | 583 | 0 | 0 | 7 |
| 50-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 50 | 7049 | 0 | 0 | 7 |
| 51-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 51 | 583 | 0 | 0 | 8 |
| 51-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 51 | 7049 | 0 | 0 | 8 |
| 52-20938 | 20938 | azijn, aceto balsamico, 25.0 ml | azijn, aceto balsamico | 25.0 ml | 52 | 583 | 0 | 0 | 9 |
| 52-30782 | 30782 | azijn, framboos, 25.0 ml | azijn, framboos | 25.0 ml | 52 | 7049 | 0 | 0 | 9 |
The ordinal is calculated based on the CW (CalendarWeek). Because current CW is 43, corresponds to Ordinal 0. Next week, will be Ordinal 1, etc.
I want to create a Pivot Table with a projected inventory view where based on the week, I can see the projected inventory.
For week with ordinal 3, the projected inventory would be (Stock at Week 0 + Inbounds(week 0,1,2,3) - Outbounds (0,1,2,3). I would like to create a measure that would give this information.
Until now I was able to come with:
CALCULATE(
IF(AVERAGE(WeekControl[Position])<0,
SUM(Full_View[Stock]),
IF(AVERAGE(WeekControl[Position])=0,
SUM(Full_View[Stock])+SUM(Full_View[Inbound])-SUM(Full_View[Forecast]),
CALCULATE(CALCULATE(SUM(Full_View[Stock]),ALL(Full_View[Ordinal])),Full_View[Ordinal]=0)
+CALCULATE(SUM(Full_View[Inbound]),FILTER(ALL(Full_View), Full_View[Ordinal] <= MAX(Full_View[Ordinal]))))))
But it's not giving the result I want. Can you help please?
I'm using Power Pivot in office365
I ended up finding a 100% working solution:
=CALCULATE ( IF ( AVERAGE ( WeekControl[Position] ) < 0, SUM ( Table1[Stock] ), IF ( AVERAGE ( WeekControl[Position] ) = 0, SUM ( Table1[Stock] ) + SUM ( Table1[Total Units Ordered] ) - SUM ( Table1[Total # Units per Ingredient] ), CALCULATE ( CALCULATE ( SUM ( Table1[Stock] ), ALL ( Table1[Ordinal] ) ), Table1[Ordinal] = 0 ) + CALCULATE ( SUM ( Table1[Total Units Ordered] ), FILTER ( FILTER ( ALLEXCEPT ( Table1, Table1[Ingredient Description] ), Table1[Ordinal] <= MAX ( Table1[Ordinal] ) ), Table1[Ordinal] >= 0 ) ) - CALCULATE ( SUM ( Table1[Total # Units per Ingredient] ), FILTER ( FILTER ( ALLEXCEPT ( Table1, Table1[Ingredient Description] ), Table1[Ordinal] <= MAX ( Table1[Ordinal] ) ), Table1[Ordinal] >= 0 ) ) ) ) )
4 Replies
- TomMartens
Super User
Hey,
I think this measure is pretty close, at least for the ordinal positions >= 0, I'm not sure about what should happen if the the ordinal position is less than 0.
My measure is called stock projection ...
stock projection= var currrentOrdinal = IF(HASONEVALUE('Table1'[ordinal]) ,MAX('Table1'[ordinal]) ,BLANK() ) var startOrdinal = IF(HASONEVALUE('Table1'[ordinal]), IF(MAX('Table1'[ordinal]) >= 0, 0,-666) ,BLANK() ) return IF(startOrdinal = 0, CALCULATE( SUM('Table1'[stock]) ,'Table1'[ordinal] = startOrdinal ) + CALCULATE( SUM('Table1'[inbound]) - SUM('Table1'[outbound]) ,FILTER(ALL(Table1[ordinal]) ,'Table1'[ordinal] >= startOrdinal && 'Table1'[ordinal] <= currrentOrdinal ) ) ,BLANK() -- here goes the formula for past weeks (ordinal less than zeor) )This allows to create a Pivot Table like so:
Please be aware of the following
Currently this measure will just work if the column ordinal is used as row or column header.
i guess it should be easy to adjust the measure, if the pivot table looks into the past (ordinal below zero), just adjust the BLANK in my measure to your liking
Hopefully this is what you are looking for.
Regards,
Tom
Hi TomMartens,
First, let me thank you for putting the effort in trying to help me! Your solution is not completely performing what I need, because it only shows the projected sale/inbound for the given ordinal week. So for outbound of ordinal week 3, it would need to sum all the outbounds for that ingredient of the ordinal week (0,1,2 and 3). The objective is that I can see clearly how the added outbounds and inbounds would progress in time.
- TomMartens
Hey,
then you have to create an additional measure like so
outbound projection = var currrentOrdinal = IF(HASONEVALUE('Table1'[ordinal]) ,MAX('Table1'[ordinal]) ,BLANK() ) var startOrdinal = IF(HASONEVALUE('Table1'[ordinal]), IF(MAX('Table1'[ordinal]) >= 0, 0,-666) ,BLANK() ) return IF(startOrdinal = 0, CALCULATE( SUM('Table1'[outbound]) ,FILTER(ALL(Table1[ordinal]) ,'Table1'[ordinal] >= startOrdinal && 'Table1'[ordinal] <= currrentOrdinal ) ) ,BLANK() )and add this measure to your Pivot Table:
Same becomes true for the inbound and maybe after all you have to iterate about the ordian using
SUMX(VALUES('Table1'[ordinal]
to get the total rightIf you need more help, please put the sample data into an Excel file, upload the Excel to onedrive or dropbox. This helps to avoid misunderstandings.
Regards,
Tom
