Forum Discussion
Ronald123
Resolver III
Resolver IIICumulative total supply
Hello,
I need a dax formule for the cumulative total supply. This is a total by the colum "Subgroep" filter by months, some items are sold.
Can anyone help my?
| Tabelname: | Stock | Tabelname: | Calender | |||||||||
| Itemcode (unique) | Subgroep | Amount | Date purchase | Date sale | Months | Measure: Cumulative total stocks | Filter | |||||
| 12 | Drinks | 1 | 2-1-2017 | 1 | Subgroup: | Drinks | ||||||
| 13 | Drinks | 1 | 3-1-2017 | 2 | Year: | 2017 | ||||||
| 14 | Drinks | 1 | 2-2-2017 | 4-4-2017 | 3 | |||||||
| 15 | Drinks | 1 | 3-2-2017 | 4 | ||||||||
| 16 | Drinks | 1 | 4-3-2017 | 5 | ||||||||
| 17 | Drinks | 1 | 5-4-2017 | 6-7-2017 | 6 | |||||||
| 21 | Fruit | 1 | 1-1-2017 | 7 | ||||||||
| 22 | Fruit | 1 | 2-3-2017 | 8 | ||||||||
| 23 | Fruit | 1 | 2-4-2017 | 9 | ||||||||
| 24 | Fruit | 1 | 2-5-2017 | 8-2-2017 | 10 | |||||||
| 25 | Fruit | 1 | 2-6-2017 | 11 | ||||||||
| 26 | Fruit | 1 | 2-7-2017 | 2-8-2017 | 12 | |||||||
| 27 | Fruit | 1 | 2-8-2018 | |||||||||
Hi Ronald123,
Please change the formula as the following one shown.sale=IF(ISBLANK(Table[Date Sale]),0,-1)
Best Regards,
Angelia
8 Replies
jmaloneYou need supply by Subgroep? In other words, you need to see the cumulative supply of Drinks, separate from the supply of Fruit, correct?
You might try this:
Cumulative Supply = CALCULATE ( SUM ( 'Stock'[Amount] ), FILTER ( ALLEXCEPT ( 'Stock', 'Stock'[Subgroep] ), 'Stock'[Date purchase] <= MAX( 'Stock'[Date purchase] ) ) , 'Stock'[Date Sale] = BLANK () ) - CALCULATE ( SUM ( 'Stock'[Amount] ), FILTER ( ALLEXCEPT ( 'Stock', 'Stock'[Subgroep] ), 'Stock'[Date purchase] <= MAX( 'Stock'[Date purchase] ) ) , 'Stock'[Date Sale] <> BLANK () )
v-huizhn-msftMicrosoft Employee
Hi Ronald123,
The total result of the subgroep fruit's must be 5, why should be 5, could you please describe the calculation rules?
Best Regards,
Angelia
