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

[OsamaAbuAsal]

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.

[v-kongfanf-msft · Accepted solution]

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]

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