Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering a table by different date column and display sales for the next x months

Hello,

I need an advice for this analytic scenario, I am not able to solve it. 

Please see attached photo.(1 is check-in date and 2 is Book Date (reservation date))

 I want to filter the table by Book Date.

So when I select 15.09.2020 in the filter I need to see the sales for  the next 12  check-in months.

 

 

Any help much appreciated.

Thank you!

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    I agree with AllisonKennedy .I suggest you add more detail information. 

    In my opinion, I think you can create a measure to filter data and use it in table visual filter pane.

    Like this:

    measure =
VAR a =
    EOMONTH ( EDATE ( SELECTEDVALUE ( table[BookDate] ), 12 ), 0 )
RETURN
    IF (
        table[CheckIn date] >= SELECTEDVALUE ( table[BookDate] )
            && table[CheckIn date] <= a,
        1,
        0
    )

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey

     

     

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous  What does your data model look like? This is the foundation to any report and we can't answer your question without knowing this information. 

     

    What table is Check In Date in? Where is Book Date? How are these tables related? Do you have a DimDate table? What other dates are in your model?

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    I agree with AllisonKennedy .I suggest you add more detail information. 

    In my opinion, I think you can create a measure to filter data and use it in table visual filter pane.

    Like this:

    measure =
VAR a =
    EOMONTH ( EDATE ( SELECTEDVALUE ( table[BookDate] ), 12 ), 0 )
RETURN
    IF (
        table[CheckIn date] >= SELECTEDVALUE ( table[BookDate] )
            && table[CheckIn date] <= a,
        1,
        0
    )

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey