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...
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.

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!!