Forum Discussion

OsamaAbuAsal's avatar
OsamaAbuAsal
Regular Visitor
2 years ago
Solved

Date Between Issue, The Selected To Column will affect the Measure Only

Dears All,

I Have 2 Fact Tables, One of them have consumed and Recieved Quantity and another one is Aggregated table that have the Avaliable Quantity, The Two facts have a DateID column and I have a Date Dimension, Find the Below:

I have Dates from 01-01-2024 To 04-01-2024, The Available Quantity will Be always from begining of the System (Igonre the selected from Date) till the Selected To Date, The Consumed and Recieved Quantity will be affected with From Date and To Date normally.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi OsamaAbuAsal ,

     

    Maybe you can refer to below formula to create measure:

    Measure_ConsumedQuantity = 
CALCULATE(
    SUM(Fact_ConsumedReceived[ConsumedQuantity]),
    FILTER(
        ALL(Dim_Date),
        Dim_Date[DateID] >= MIN(Dim_Date[DateID]) &&
        Dim_Date[DateID] <= MAX(Dim_Date[DateID])
    )
)
    Measure_ReceivedQuantity = 
CALCULATE(
    SUM(Fact_ConsumedReceived[ReceivedQuantity]),
    FILTER(
        ALL(Dim_Date),
        Dim_Date[DateID] >= MIN(Dim_Date[DateID]) &&
        Dim_Date[DateID] <= MAX(Dim_Date[DateID])
    )
)
    Measure_AvailableQuantity = 
CALCULATE(
    SUM(Fact_Available[AvailableQuantity]),
    FILTER(
        ALL(Dim_Date),
        Dim_Date[DateID] <= MAX(Dim_Date[DateID])
    )
)
    Measure_TotalQuantity = [Measure_AvailableQuantity] + [Measure_ReceivedQuantity] - [Measure_ConsumedQuantity]

    Best Regards,
    Adamk Kong

     

    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
    Super User
    2 years ago

    Hi OsamaAbuAsal - Create a measure for consumed Quantity with min and max dates as below:

    Consumed Quantity =
    CALCULATE(
    SUM(Fact_Consumed_Received[ConsumedQuantity]),
    FILTER(
    ALL(Dim_Date),
    Dim_Date[Date] >= MIN(Dim_Date[Date]) && Dim_Date[Date] <= MAX(Dim_Date[Date])
    )
    )

     

    like wise create another measures for reseaved quantity metric and available quantity

     

    Received Quantity =
    CALCULATE(
    SUM(Fact_Consumed_Received[ReceivedQuantity]),
    FILTER(
    ALL(Dim_Date),
    Dim_Date[Date] >= MIN(Dim_Date[Date]) && Dim_Date[Date] <= MAX(Dim_Date[Date])
    )
    )

     

    create the same with your fact with available quantity and use it.

    Hope it works

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi OsamaAbuAsal ,

     

    Maybe you can refer to below formula to create measure:

    Measure_ConsumedQuantity = 
CALCULATE(
    SUM(Fact_ConsumedReceived[ConsumedQuantity]),
    FILTER(
        ALL(Dim_Date),
        Dim_Date[DateID] >= MIN(Dim_Date[DateID]) &&
        Dim_Date[DateID] <= MAX(Dim_Date[DateID])
    )
)
    Measure_ReceivedQuantity = 
CALCULATE(
    SUM(Fact_ConsumedReceived[ReceivedQuantity]),
    FILTER(
        ALL(Dim_Date),
        Dim_Date[DateID] >= MIN(Dim_Date[DateID]) &&
        Dim_Date[DateID] <= MAX(Dim_Date[DateID])
    )
)
    Measure_AvailableQuantity = 
CALCULATE(
    SUM(Fact_Available[AvailableQuantity]),
    FILTER(
        ALL(Dim_Date),
        Dim_Date[DateID] <= MAX(Dim_Date[DateID])
    )
)
    Measure_TotalQuantity = [Measure_AvailableQuantity] + [Measure_ReceivedQuantity] - [Measure_ConsumedQuantity]

    Best Regards,
    Adamk Kong

     

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