Forum Discussion

nleuck's avatar
nleuck
Icon for Post Patron rankPost Patron
6 years ago
Solved

Waterfall chart need to remove next month

Hello All,

 

I need some help with my Waterfall Chart. I have two datasets. One table is ticket info and the other is a date table. For Category I'm using a MonthYear column from my date table and my Y-Axis is the Monthly Var Ticket Count (calculated: current ticket count - previous month ticket count.) My only issue is that the next month May 2020 shows up in my chart. Is there a way to get the chart to end at current month?

 

Thanks,

 

Need Help
  • nleuck's avatar
    nleuck
    6 years ago

    I found a solution that works for this issue. I created a column in my Date table:

     

    IsFutureMonth: IF(EOMONTH('Date'[Date], 0) > EOMONTH(TODAY(), 0), "1", "0")

     

    That will mark those months that are future months in my Date table. I use that as a visual filter on my Waterfall chart and select 0, so none of the future months show up.

     

    Thanks for everyone's help!

14 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi nleuck ,

     

    Till what date you have data in Ticket Info table? Your chart shows there is data for May 2020, that's why it is displayed.

     

    If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

     

    Thanks,

    Pragati

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        Hi nleuck ,

         

        Your date is coming from DATE table right, which is in relationship with TicketInfo table on Date column?

        If this is the case, then your chart should display data only till 21st April 2020.

        I re-created a similar scenario.

        1. Date table has data from 1st Jan 2020 to 31st Mar 2020.
        2. Summary table has data in the date range 1st Mar 2020 to 31st Mar 2020

        Then I create the following chart using DATE value on x-axis from DATE TABLE and get the following:

        This looks perfect at my end.

         

        Can you share your sample data to check why it's not working at your end?

         

        If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

         

        Thanks,

        Pragati

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi nleuck ,

     

    1. If the date of your date table is from 1/1/2020 to 5/20/2020, you will get this result.

    Picture1 

    2. If the date of your date table is from 1/1/2020 to 4/20/2020, you will get this result.

    Picture2 

     

    Is the second result you want?

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • nleuck's avatar
      nleuck
      Icon for Post Patron rankPost Patron
      6 years ago

      v-lionel-msft 

       

      My date table is actually a full calendar. It's max date is Dec 31 2020. So I went into Query Editor and in my embedded query I added a WHERE clause Date <= GETDATE(). That way it only retrieve current dates that have happened. The issue with that is now my Waterfall chart doesn't show any data. The months show correctly but there is no data in the visual.

      • v-lionel-msft's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi nleuck ,

         

        You can put the [Previous Month Ticket Count] measure in the table visual and check if it returns the correct value. If not, obviously you have a problem with the date table filtering(You may need to check the M code).

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

         

  • nleuck's avatar
    nleuck
    Icon for Post Patron rankPost Patron
    6 years ago

    I found a solution that works for this issue. I created a column in my Date table:

     

    IsFutureMonth: IF(EOMONTH('Date'[Date], 0) > EOMONTH(TODAY(), 0), "1", "0")

     

    That will mark those months that are future months in my Date table. I use that as a visual filter on my Waterfall chart and select 0, so none of the future months show up.

     

    Thanks for everyone's help!