Forum Discussion

wendereis's avatar
wendereis
Icon for Helper I rankHelper I
1 year ago
Solved

Power BI - Help Group Quantity and Subtracted Especific Absolute Total Value in Columns

Hi..
I have a situation as example below:

I have a grouped quantity of materials as in table below by currently date line and 25 bins avalible in stock --- fixed value).

 

Available Stock: 25 bins (fixed value)  -->> It can be put in a specific table or in each of the tables below

Table A:

Currently Date     | Supply quantity: | Supply Date:  | Demand Quantity:  | Demand Date: |     Result >> Stock capacity:
ID: 1 10/102024   | >> - 2 EA           | 09/10/2024    |          + 01 EA            | 10/10/2024   |   +24 << (result of: 25-2+1).
ID: 2 10/102024   | >> - 2 EA           | 09/10/2024    |         + 01 EA             | 12/10/2024   |    +22 << (result of: 24-2).

 

In the ID 2 the demand quantity doesn't need being subtracted cause da currently demand (12/10/2024) date column is greater than currently date column (10/10/2024).


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Need 2 (another visual report)

Example 2 by month:


I have a grouped quantity of materials as in table below by currently date line and 25 bins avalible in stock --- fixed value).

Available Stock: 25 bins (fixed value)

Table B (another example)

    Currently Date          | Supply quantity:  | Supply Date:      |     Demand Quantity: | Demand Date:   |     result>> Stock capacity:    |
ID: 1 2024 Octuber       | >> - 2 EA            | 2024 Octuber     |             + 01 EA         | 2024 Octuber     |    +24 << (result of: 25-2+1).
ID: 2 2025 November   | >> - 2 EA             | 2025 November |            + 01 EA        | 2025 November |    +23 <<(result of: 24-2+1).

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi wendereis ,
    Based on your description, I created the following sample data

    ID Currently Date Supply quantity Supply Date Demand Quantity Demand Date
    1 10/10/2024 2 10/9/2024 1 10/10/2024
    2 10/10/2024 2 10/9/2024 1 10/12/2024
    3 10/11/2024 3 10/9/2024 2 10/13/2024

    Create two columns

    Result1 = 
VAR InitialValue = 25
VAR _ID ='Table'[ID]
RETURN
IF(
    'Table'[Demand Date] < 'Table'[Currently Date],
    InitialValue - SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Supply quantity]) + SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Demand Quantity]),
    InitialValue - SUMX(FILTER('Table','Table'[ID] <= _ID),'Table'[Supply quantity]) + SUMX(FILTER('Table', 'Table'[ID] <= _ID && 'Table'[Demand Date] <= 'Table'[Currently Date] ), 'Table'[Demand Quantity])
)
    Result2 = 
VAR InitialValue = 25
VAR _ID = 'Table'[ID]
RETURN
InitialValue - SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Supply quantity]) + SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Demand Quantity])

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi wendereis - You can display the results in a table visual to list all entries with the respective stock capacity per date or month.You may also want to visualize the trend in available stock capacity over time using a line chart to track changes dynamically based on the calculations.

    You can use the built-in grouping functionality in Power BI to aggregate data by month.

     

    can you please share the data in excel copy format with input and what is expected output? It helps.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi wendereis ,
    Based on your description, I created the following sample data

    ID Currently Date Supply quantity Supply Date Demand Quantity Demand Date
    1 10/10/2024 2 10/9/2024 1 10/10/2024
    2 10/10/2024 2 10/9/2024 1 10/12/2024
    3 10/11/2024 3 10/9/2024 2 10/13/2024

    Create two columns

    Result1 = 
VAR InitialValue = 25
VAR _ID ='Table'[ID]
RETURN
IF(
    'Table'[Demand Date] < 'Table'[Currently Date],
    InitialValue - SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Supply quantity]) + SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Demand Quantity]),
    InitialValue - SUMX(FILTER('Table','Table'[ID] <= _ID),'Table'[Supply quantity]) + SUMX(FILTER('Table', 'Table'[ID] <= _ID && 'Table'[Demand Date] <= 'Table'[Currently Date] ), 'Table'[Demand Quantity])
)
    Result2 = 
VAR InitialValue = 25
VAR _ID = 'Table'[ID]
RETURN
InitialValue - SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Supply quantity]) + SUMX(FILTER('Table', 'Table'[ID] <= _ID), 'Table'[Demand Quantity])

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly