Date Filtering - Use Max Date even if blank

[astano05]

I have a date slicer. I want to use the last date in the range in a measure. Right now using Max or LastDate will take the last non-blank. 

For example, with order data i want a measure for the quantity ordered on the last date of the date range. 

Slicer = 1/1/2021-8/4/2021

If a customer had no orders on 8/4 it will take the next non-blank date and use that for the measure. I want it to use 8/4 no matter what. 

How can I accomplish this?

[ryan_mayu]

@astano05 

do you have a date table? could you pls provide the sample data?

[astano05]

i do not have a date table in this set.

I have a table with order headers (order level) connected to a table with order line information (item level), as well as tables with customer information and item information. 

[v-shex-msft]

HI @astano05,

Please provide some dummy data to help us clarify your table sculpture and relationship, then we can test to coding formula on it.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[ryan_mayu · Accepted solution]

@astano05 

maybe you can try create a date table, create relationship with your table. Use the date from date table in the slicer. Then you can get maxdate = max(date[date])