Forum Discussion
Rolling Count and Sum for Order Balance
Hello All,
Below is the table. Task Completed date is the slicer. I am trying to calculate count of distinct orders and Orders in balance
Count of distinct orders = Calculate(distinctcount(Order No))
Order Sum = Calculate(SUM(Order Count),ALLEXCEPT(table, Order no),Filter(table, Task completed date = SELECTEDVALUE(Date[Date]))) -- When default date slicer is selected for Order Sum it's displaying null instead of 1
Count of distinct orders in balance = Calculate(distinctcount(Order No),Filter(Order Sum = 0))
For a instance, if I selected order no 100 and task completed date in the range 4/3/20 to 4/7/2020 then it should display following
Count of distinct Orders =1
Count of distinct Orders in balance = 0
Order Sum = -100
For another instance, if I selected order no 100 and task completed date in the range 4/7/20 to 4/7/2020 then it should display following
Count of distinct Orders =1
Count of distinct Orders in balance = 1
Order Sum = 0
Please help!!
| Order No | Order Completed Date | Task Order No | Task Completed Date | Order Count |
| 100 | 4/8/20 | 100-1 | 4/7/20 | 0 |
| 100 | 4/8/20 | 100-1 | 4/7/20 | 0 |
| 100 | 4/8/20 | 100-1 | 4/7/20 | 0 |
| 100 | 4/8/20 | 100-4 | 4/3/20 | 0 |
| 100 | 4/8/20 | 100-4 | 4/3/20 | -100 |
| 200 | 3/8/20 | 200-1 | 3/7/20 | 0 |
| 200 | 3/8/20 | 200-2 | 3/7/20 | 10 |
| 200 | 3/8/20 | 200-3 | 3/7/20 | 5 |
| 200 | 3/8/20 | 200-4 | 3/8/20 | -15 |
| 200 | 3/8/20 | 200-4 | 3/8/20 | 15 |
| 200 | 3/8/20 | 200-4 | 3/8/20 | -15 |
| 200 | 3/8/20 | 200-4 | 3/8/20 | 0 |
Hi Anonymous ,
Try to modify your [Order Sum] measure like so:
Order Sum = CALCULATE ( SUM ( 'Table'[Order Count] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Order No] ), 'Table'[Task Completed Date] IN VALUES ( 'Date'[Date] ) ) )BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , you should join it task completed date with the date of date table. In case you have more than one date to join use userelation for inactive joins
Order Sum = Calculate(SUM(Table[Order Count]),ALLEXCEPT(table, Table[Order no]))
Order Sum = Calculate(SUM(Table[Order Count]))
Check how to deal with more than one date and date dimension join
Hi Anonymous ,
Try to modify your [Order Sum] measure like so:
Order Sum = CALCULATE ( SUM ( 'Table'[Order Count] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Order No] ), 'Table'[Task Completed Date] IN VALUES ( 'Date'[Date] ) ) )BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
