How to evaluate Data where there are 2 Date columns in Fact table connected with Dim Date.

[Ashutosheto]

Hello Everyone,

I have one Fact_weight Table and a Dim_Date Table. Here the Dim_Date Datekey is connected with DateInKey column from Fact_weight. 

I have a slicer from DimDate which uses start date and end date. As Start Date i set 01/01/2023 and end Date i set 28/02/2023.

My Goal is to get sum of Weight where DateIn >=01/01/2023 & DateIn <=28/02/2023, and DateOut >28/02/2023. 
Both the filters should be correct and if there is any entry where both filters are true, then i want sum of those weights.

here in Table the last 2 entries satisfy the filter hence the result i want is 2.4+3.4 =5.8

I want Result to show in Table and the table has following columns.
Date (Dim_Date)

Weight(Fact_weight)
DateIn(Fact_Weight)

DateOut(Fact_weight).
Here because i am using Date(Dim_Date) as a column, the second criteria of filter (DateOut >28/02/2023) is just ignored.

Here is the Measure which i used :

MATERIAL_IN_BUNKER=

VAR MinDateKey    =  CALCULATE(MIN(DimDate[DateKey]), ALLSELECTED(DimDate[DateKey]))

VAR targetDateKey = CALCULATE(MAX(DimDate[DateKey]), ALLSELECTED(DimDate[DateKey]))

var Result=

CALCULATE(

    SUM(Fact_weight[Weight]),

    REMOVEFILTERS(DimDate[DateKey]),

    KEEPFILTERS(Fact_Weight[DateInKey]>=MinDateKey && Fact_Weight[DateInKey]<=targetDateKey),

    KEEPFILTERS(Fact_Weight[DateOutKey]>targetDateKey)

)

RETURN
Result

I would really appreciate if anyone can help me with this Problem. Thanking you in advance :))

Fact_Weight

Dim_Date

[Anonymous · Accepted solution]

Hi @Ashutosheto ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Use a date table that is not related to the Fact_weight Table and put the date table into the slicer.

Measure =
VAR _maxdate =
    MAXX ( ALLSELECTED ( dim_date ), dim_date[Date] )
VAR _mindate =
    MINX ( ALLSELECTED ( dim_date ), dim_date[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[weight] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date in] >= _mindate
                && 'Table'[Date in] <= _maxdate
                && 'Table'[date out] >= _maxdate
        )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Ashutosheto]

Hi @Anonymous ,
Thank you so much for the reply and yes it worked. I changed the measure a bit and got the desired Result. I am writing the new Measure and also attaching the result which i wanted. Thank you 🙂
This is the new Measure 

Measure2 =

var _maxdate=MAXX(ALLSELECTED(dim_date),dim_date[Date])

var _mindate=MINX(ALLSELECTED(dim_date),dim_date[Date])

return

CALCULATE(

    SUM('Table'[weight]),

    REMOVEFILTERS(dim_date),

    'Table'[Date in]>=_mindate&&'Table'[Date in]<=_maxdate&&'Table'[date out]>=_maxdate)Result