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

     

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi wolfjnh 

      It's hard to provide the accurate solution without looking at sample data.Please check the attached sample file for reference and show me your simplified sample data to reproduce your scenario.

      Regards,

      • wolfjnh's avatar
        wolfjnh
        Frequent Visitor
        7 years ago

        Hi v-cherch-msft ,

         

        please find attached the pbix-file with all the measures and informations you will probably need.

        The fake measures shows which values I would expect according to the slicer.

         

        I had some issues with the english dateformat and changed it. Hope it works for you. 

         

        pbix - Dropbox

        Is there any possibility for me to upload directly to this page as you did it?

        Regards,

        Jan