Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-yuta-msft
Community Support
Community Support

@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
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.