Summarize table by a DimDate table

[Anonymous]

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.

[v-yuta-msft]

@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.

[Anonymous]

Anyone knows how to calculate the stock with this logic in DAX?

[Anonymous]

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.