Three Dates Filter Problem

[SG1080]

Hi there ,

following challenge. I have a KPI that I want to filter on three different dates. On the one hand, I want the order to be no older than 3 months and that the shipping must not be more than 1 month old. I have three date tables Date;Date_Order;Date_Shipping. All tables have a month index column. All date tables are linked to the fact table. Does anyone have an idea how I can solve this using a measure?

[v-luwang-msft · Accepted solution]

Hi @SG1080 ,

Test like the below :

1.create a slicer table :

dataslicer = CALENDAR("2021,1,1","2022,12,31")

2.then create measure:( the order to be no older than 3 months and that the shipping must not be more than 1 month old.)

test =
IF (
    DATEDIFF (
        MAX ( 'Table'[Order date] ),
        SELECTEDVALUE ( dataslicer[Date] ),
        DAY
    ) >= 0
        && DATEDIFF (
            MAX ( 'Table'[Order date] ),
            SELECTEDVALUE ( dataslicer[Date] ),
            DAY
        ) <= 90
        && DATEDIFF (
            MAX ( 'Table'[Shipping Date] ),
            SELECTEDVALUE ( dataslicer[Date] ),
            DAY
        ) >= 0
        && DATEDIFF (
            MAX ( 'Table'[Shipping Date] ),
            SELECTEDVALUE ( dataslicer[Date] ),
            DAY
        ) <= 30,
    1,
    0
)

Apply filter  and output,you could select the date you want to apply:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@SG1080 , For shipping and order date you can have filters like

Calculate(sum(Table[Count]), filter(Table, Table[Order Date] >= eomonth(today(),-3)  && Table[Shipping Date] >= eomonth(today(),-1) ) )