Forum Discussion
Summarize table by a DimDate table
I have data like this (you can copy/paste into Enter Data, dates in dd/MM/yyyy):
| GROUP | IN | OUT | Quantity |
| A | 01/01/2018 | 05/01/2018 | 1 |
| A | 01/01/2018 | 04/01/2018 | 1 |
| A | 02/01/2018 | 06/01/2018 | 1 |
| A | 02/01/2018 | 03/01/2018 | 1 |
| A | 03/01/2018 | 07/01/2018 | 1 |
| B | 01/01/2018 | 05/01/2018 | 1 |
| B | 01/01/2018 | 04/01/2018 | 1 |
| B | 02/01/2018 | 06/01/2018 | 1 |
| B | 02/01/2018 | 03/01/2018 | 1 |
| B | 03/01/2018 | 07/01/2018 | 1 |
| C | 01/01/2018 | 05/01/2018 | 1 |
| C | 01/01/2018 | 04/01/2018 | 1 |
| C | 02/01/2018 | 06/01/2018 | 1 |
| C | 02/01/2018 | 03/01/2018 | 1 |
| C | 03/01/2018 | 07/01/2018 | 1 |
Each row is a transaction. The table shows when the quantity of something had entered and left each group.
I want the "stock" in each group, each day. So for each day, sum quantity for the rows where
IN <= day && OUT > day
For group A, on 03/01/2018 you have 4 units (1º, 2º, 3º and 5º row, 4º row does not match the condition).
The expected result would be something like:
Or like this:
My attempt has been to make an unrelated dimdate table:
Calendar = CALENDAR(MIN(Process[IN]); MAX(Process[OUT]))
And try to use SUMMARIZECOLUMNS:
Process_Calculated =
SUMMARIZECOLUMNS(
Calendar[Date];
Process[GROUP];
"Quantity"; CALCULATE(
SUM(Process[Quantity]);
'Calendar'[Date] <= SELECTEDVALUE(Process[IN]]) && 'Calendar'[Date] > SELECTEDVALUE(Process[OUT])
)
)But dates can´t be compared like this (also I think SUMMARIZECOLUMNS doesn´t have row context).
I have to use DAX (can´t use PowerQuery) beacause I plan to use this table inside a VAR in a measure so it is dynamic to slicers.
3 Replies
Anonymous ,
Each row is a transaction. The table shows when the quantity of something had entered and left each group.
I want the "stock" in each group, each day. So for each day, sum quantity for the rows where
IN <= day && OUT > day
For group A, on 03/01/2018 you have 4 units (1º, 2º, 3º and 5º row, 4º row does not match the condition).
The expected result would be something like:
There's no "day" column in your sample data. Could you clarify more details about your logic?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-yuta-msft ,
yeah, sorry. "Day" refers to every day in a calendar, I guess the best way to do this is with a calendar table not-connected to the main table.
The main table gives the IN/OUT date and the quantity, but I need to calculate the stock for every day, so another calendar table is needed.
