Forum Discussion

maurcoll's avatar
maurcoll
Helper IV
4 months ago
Solved

Looking at future orders based on date selection

Hi is it possible to have a date slicer and multiple tables on a page. Each table covers a months orders going forward. I want to be able to select a 3 month period in the future and have 3 tables on...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    4 months ago

    Step 1) Create month offset measures

    Month 1 Start =
    MIN ( 'Date'[Date] )

Month 1 End =
    EOMONTH ( MIN ( 'Date'[Date] ), 0 )

Month 2 Start =
    EOMONTH ( MIN ( 'Date'[Date] ), 0 ) + 1

Month 2 End =
    EOMONTH ( MIN ( 'Date'[Date] ), 1 )

Month 3 Start =
    EOMONTH ( MIN ( 'Date'[Date] ), 1 ) + 1

Month 3 End =
    EOMONTH ( MIN ( 'Date'[Date] ), 2 )

     

    Step 2) Create a filter measure per table

    Is Month 1 =
IF (
    MAX ( Orders[Date] ) >= [Month 1 Start]
        && MAX ( Orders[Date] ) <= [Month 1 End],
    1,
    BLANK ()
)

Is Month 2 =
IF (
    MAX ( Orders[Date] ) >= [Month 2 Start]
        && MAX ( Orders[Date] ) <= [Month 2 End],
    1,
    BLANK ()
)

Is Month 3 =
IF (
    MAX ( Orders[Date] ) >= [Month 3 Start]
        && MAX ( Orders[Date] ) <= [Month 3 End],
    1,
    BLANK ()
)


    Step 3) Apply each filter measure to its corresponding table visual

    On Table 1: Filters on this visual → drag Is Month 1 → set to is 1. On Table 2: Filters on this visual → drag Is Month 2 → set to is 1. On Table 3: Filters on this visual → drag Is Month 3 → set to is 1.

luisoliveira89's avatar
luisoliveira89
Helper I
4 months ago

Hi maurcoll , this is the approach I would follow

 

Create a Date table and use it in the slicer. Then create 3 measures, each one offsetting the selected period by month.

 

You can do this by capturing the min selected date and then filtering each table with a different month offset using something like EDATE().

Each table visual then uses a measure like:

  • Month 1 → between StartDate and EOMONTH(StartDate, 0)
  • Month 2 → between EDATE(StartDate, 1) and EOMONTH(StartDate, 1)
  • Month 3 → same logic with +2

Put the same dataset in all 3 tables, but apply a different measure/filter to each.