cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlejandroG Regular Visitor
Regular Visitor

Summarize table by a DimDate table

I have data like this (you can copy/paste into Enter Data, dates in dd/MM/yyyy):

GROUPINOUTQuantity
A01/01/201805/01/20181
A01/01/201804/01/20181
A02/01/201806/01/20181
A02/01/201803/01/20181
A03/01/201807/01/20181
B01/01/201805/01/20181
B01/01/201804/01/20181
B02/01/201806/01/20181
B02/01/201803/01/20181
B03/01/201807/01/20181
C01/01/201805/01/20181
C01/01/201804/01/20181
C02/01/201806/01/20181
C02/01/201803/01/20181
C03/01/201807/01/20181

 

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:

Group2.JPG

Or like this:
Group3.JPG

 

My attempt has been to make an unrelated dimdate table:

Calendar = CALENDAR(MIN(Process[IN]); MAX(Process[OUT]))

Group4.JPG

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 3
Community Support Team
Community Support Team

Re: Summarize table by a DimDate table

@AlejandroG ,

 

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.

AlejandroG Regular Visitor
Regular Visitor

Re: Summarize table by a DimDate table

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.

AlejandroG Regular Visitor
Regular Visitor

Re: Summarize table by a DimDate table

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 113 members 1,621 guests
Please welcome our newest community members: