Forum Discussion
Cumulative total between date slicer
Hi there,
I am trying to filter a list of cumulative Inventory with a date slicer.
It works when I move the "until" side, but when I move the "from" side, it shows wrong numbers.
Example "Sale" table which is used for calculating:
| Item | InQty | OutQty | Date |
| A | 20 | 2 | 2022/07/14 |
| A | 5 | 2022/07/15 | |
| A | 4 | 2022/07/16 | |
| A | 8 | 2022/07/17 | |
| A | 15 | 3 | 2022/07/18 |
| A | 5 | 2022/07/19 | |
| A | 2 | 2022/07/20 | |
| A | 10 | 6 | 2022/07/21 |
I also have a Date Table which is used for the slicer and has relationship to the Sales table.
Following measure is used:
Cumulative Total Inventory=
The resulting table would be
| Cumulative Total inventory | Date |
| 18 | 2022/07/14 |
| 13 | 2022/07/15 |
| 9 | 2022/07/16 |
| 1 | 2022/07/17 |
| 13 | 2022/07/18 |
| 8 | 2022/07/19 |
| 6 | 2022/07/20 |
| 10 | 2022/07/21 |
| Cumulative Total inventory | Date |
| 8 | 2022/07/19 |
| 6 | 2022/07/20 |
| 10 | 2022/07/21 |
temuulenchoi , You should use all as inventory need to build from start
Cumulative Total Inventory=
CALCULATE(
SUM(Sale[InQty])-SUM(Sale[OutQty]),
FILTER(ALL(DateTable[Date]),
DateTable[Date] <= max(DateTable[Date]) && (DateTable[Date]) <= TODAY()))
6 Replies
- amitchandak
Super User
temuulenchoi , You should use all as inventory need to build from start
Cumulative Total Inventory=
CALCULATE(
SUM(Sale[InQty])-SUM(Sale[OutQty]),
FILTER(ALL(DateTable[Date]),
DateTable[Date] <= max(DateTable[Date]) && (DateTable[Date]) <= TODAY()))Thanks! This worked very well
amitchandak, now I'm trying to count the out of stock dates. Imagine if the inventory was 0 between 2022/07/25 and 2022/07/28, the following measure returns 4.
Days out of stock =COUNTROWS(FILTER(ADDCOLUMNS(CROSSJOIN( VALUES(DateTable[Date]), VALUES(Sale[Item])),"Inventory Levels", [Cumulative Total Inventory]),[Inventory Levels] = 0 && NOT(ISBLANK([Inventory Levels]))))
but if i start the slicer from 2022/07/26 for example, it returns blank. Could you help me with this? Thank you very much in advance!
ryan_mayuwhat's the logic of calculating the inventory? It's because what i calculate is different from yours. Could you pls explain more on this?
I'm sorry, there must have been an error on copying the table. I just edited the post with the correct table. Thanks!
- ryan_mayu
pls see the attachment and check if this is what you want.
