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, that there are historical data in the table)

 

I am using a date dimension which is connected to shipping_date.

 

Here is what I want:

I want to visualize the value in a monthly chart based on the shipping_date and get it filtered by a date slicer from date dimension, which works fine. 

 

But:

As there are historical data in this table, I just want to show the data of the latest load_date, which is still inside the filtered time period.

 

As far as I understood, I can't use a calculated column, because it will always show the latest load_date without filtering.

So I tried it like this with a measure:

Sum dynamic = 
var max_shipping_date = MAX('Date'[shipping_date])

var max_load_date = 
CALCULATE(
    MAX(Table[load_date]);
    Table[load_date] < max_shipping_date
)

return
CALCULATE(
    SUM(Table[Value]);
    Table[load_date] =  max_load_date
)

This works as long I don't want to show it on a montly chart. Then it gives me the max load_date by month....

 

I believe, that there is a simple solution, but I just can't fint it. 

Maybe someone has an idea?


Thanks!

 

 

 

 

Need Help

6 Replies

  • 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,