Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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.
Anyone knows how to calculate the stock with this logic in DAX?
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |