Forum Discussion

wolfjnh's avatar
wolfjnh
Frequent Visitor
7 years ago

Filtering two dates in one table

Hi,   I have a table, where every row is representing a commitment. This table has following columns (simplified) * shipping_date * value * description * load_date (<- this leads to the fact, ...
Need Help
v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi wolfjnh 

You may try below measure to get the max load_date by month.

Measure =
VAR max_shipping_date =
    MAX ( 'Date'[Date] )
VAR max_load_date =
    CALCULATE (
        MAX ( 'Table'[load_date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            MONTH ( 'Table'[load_date] ) = MONTH ( MAX ( 'Table'[load_date] ) )
                && 'Table'[load_date] < max_shipping_date
        )
    )
RETURN
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[load_date] = max_load_date )

Regards,

wolfjnh's avatar
wolfjnh
Frequent Visitor
7 years ago

Hi Cherie Chen,

 

thank you for your answer. I think ALLSELECT is a good guess. 

Unfortunately I don't get the expected result. Here is a table where you can see the expected data (faked), the data of my own measure and the data of your measure. I also added the max_load_date as a separate measure.

As you can see, I just get the data of the last month and not all the data of the last load_date.

 

The more I read about ALLSELECTED, I think, that this is the right direction...

 

Regards,

Jan