Forum Discussion

SG1080's avatar
SG1080
Frequent Visitor
4 years ago
Solved

Three Dates Filter Problem

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?
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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