Filtering two dates in one table

[wolfjnh]

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!

[v-cherch-msft]

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]

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]

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]

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

[v-cherch-msft]

Hi @wolfjnh 

You may disconnect the relationship and use below measure:

Measure (yours) =
VAR min_date =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[load_date] = min_date )
    )

Regards,

[wolfjnh]

Hi @v-cherch-msft ,

thank you for your solution. To be honest, it is not the solution I need, but it is probably the nearest, we can get.

I will think about the data schema again and maybe I can avoid this problem.

Thank you for your time!