Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Representing Month by data in the chart

Hello Folks

 

Trying to provide a chart that presents the view of the monthly Total Invoices & Receipts by customer. I tried using the water fall chart and i am having a hard time to figure out how to keep the Months in the order in the chart. 

 2 Questions.

Is Waterfall chart the right one to repersent this and is there a better way to display the months in the order? 

 

Customer Table 

Customer

2000
2005
2008
2793

 

Invoices Table

Customer  Total Invoice   Invoice Date

2000186542/7/2019
200584322/8/2019
200099761/8/2019
279358214/2/2019
200819044/2/2019

 

Customer Total Receipt  Receipt Date

2000150322/20/2019
200091001/25/2019
200564323/1/2019
200520003/1/2019

 

Calendar table has corresponding Month & year. 

DateMonth in Calendar
  
1/1/2019Jan-19
2/1/2019Feb-19
3/1/2019Mar-19
4/1/2019Apr-19

 

My chart has Month & year in break down and customer in category

 

 

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. Here is the relationship.

    Calendar(a calculated table): 

    Calendar = CALENDARAUTO()

     

    If you use waterfall chart, you may create two charts. One is for 'Total Invoice' and another is for 'Total Receipt'. Please make sure you drag 'Year' and 'Month' column in date hierarchy from Calendar table. Then you can click 'Expand all down one level in the hierarchy'. 

     

    You can also use a clustered column Chart. Then you may repeat the steps as above.

     

    Best Regards

    Allan

     

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

5 Replies

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

    Create a new field in your date table. And make it sort column of moth year

     

    month year sort = format(Date[date],"YYYYMM")

     

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Proud to be a Datanaut Connect on Linkedin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

       

      That did not work. I added new column as suggested and changed to month year sort.

       

       

       

       

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

        On ... there should be option to sort, just check the graph is sort on month year

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

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. Here is the relationship.

    Calendar(a calculated table): 

    Calendar = CALENDARAUTO()

     

    If you use waterfall chart, you may create two charts. One is for 'Total Invoice' and another is for 'Total Receipt'. Please make sure you drag 'Year' and 'Month' column in date hierarchy from Calendar table. Then you can click 'Expand all down one level in the hierarchy'. 

     

    You can also use a clustered column Chart. Then you may repeat the steps as above.

     

    Best Regards

    Allan

     

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