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 the page each one showing one months data. so for example in the date slicer i have selected 1st November 2026 to 31st January 2027. The first table will be for November, second for December etc.. I need them to change based on the date range selected so if 1st October 2026 to 31st December 2026 were selected then the first table will be for October etc... 

DateOrders
01/11/202625
02/11/202613
03/11/202615

 

 

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.

6 Replies

  • mickey64's avatar
    mickey64
    Super User
    4 months ago

    For your reference.

     

    Step 0: I use these DATA below.

     

    Step 1: I make a 'Calendar' table  and add a relationship.

     

     

    Step 2: I make three measures and a slicer and four tables below.

        M_Sum = SUM(DATA[Orders])

        M_Sum Month+1 = CALCULATE([M_Sum],DATEADD('Calendar'[Date],1,MONTH))
        M_Sum Month+2 = CALCULATE([M_Sum],DATEADD('Calendar'[Date],2,MONTH))

     

  • 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.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 months ago

    maurcoll 

    you need to have a date slicer table and don't create relationship between tables.

    then create three measures

     

    Measure =
    var _date=max('Table2'[month])
    return if(year(max('Table'[Date]))*100+month(max('Table'[Date]))=_date,1)
     
    Measure 2 =
    var _date=EDATE(max('Table2'[Date]),1)
    var _date2=year(_date)*100+month(_date)
    return if(year(max('Table'[Date]))*100+month(max('Table'[Date]))=_date2,1)
     
    Measure 3 =
    var _date=EDATE(max('Table2'[Date]),2)
    var _date2=year(_date)*100+month(_date)
    return if(year(max('Table'[Date]))*100+month(max('Table'[Date]))=_date2,1)
     
    apply each measure to each table and set to 1
     
    pls see the attachment below
     
     
  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    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.

  • v-menakakota's avatar
    v-menakakota
    Community Support
    4 months ago

    Hi maurcoll ,
    Thanks for reaching out to the Microsoft fabric community forum. 


    I would also take a moment to thank mickey64   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
    I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

    Best Regards, 
    Community Support Team

     

    • v-menakakota's avatar
      v-menakakota
      Community Support
      4 months ago

      Hi maurcoll ,

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

      Best Regards, 
      Community Support Team