Forum Discussion

SabineOussi's avatar
SabineOussi
Skilled Sharer
10 years ago
Solved

Monthly Comparison

Hello,   I'm trying to generate a report similar to the below where I want to compare specific attributes between two consecutive months.   Date is given in full date format so whenever I ...
Need Help
Tips and Tricks
  • austinsense's avatar
    austinsense
    10 years ago

    Great question - you have two options. Sounds like you might want to start with #1 and see how that goes ...

     

    1. Create a calculated column that returns the month and year. For example, FORMAT(Table[Date], "yyyy-mm"), that will transform "Mar 15, 2016 12:30 AM" into "2016-03"

     

    2. Add a date table to your data model that already includes the month rollup.

austinsense's avatar
austinsense
Impactful Individual
10 years ago

Great question - you have two options. Sounds like you might want to start with #1 and see how that goes ...

 

1. Create a calculated column that returns the month and year. For example, FORMAT(Table[Date], "yyyy-mm"), that will transform "Mar 15, 2016 12:30 AM" into "2016-03"

 

2. Add a date table to your data model that already includes the month rollup.

  • SabineOussi's avatar
    SabineOussi
    Skilled Sharer
    10 years ago

    That was easy! Thanks.

     

    I actually formatted the month-year to a date type and now they are sorted chronologically.

     

    One more thing, I know that I can sort the total of the columns but is there any way I can sort the data values according to each month?

    Based on the screenshot provided:

    - if sorted by Feb (largest to smallest): customer 5, 3, 1, 2, 4

    - if sorted by Jan (largest to smallest): customer 5, 1, 3, 2, 4

     

    Thanks again.

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Hi SabineOussi,

       

         I give you another solution for your problem (if you want). With a sample dataset I create a "matrix visualization", and then create a calculated column to display the data like you want.

       

      About the sort of the months, you can keep "YearMonth" like text and sort it by the "date" column

       

       

      About the sort order that you want create, I can tell you that, if you click on the january of february column they'll ordered. But I don't understand why the sort is based only on the february column. Sorry about that..

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      And you obtain this:

       

       

       

      #I'M Not An Expert#

      • SabineOussi's avatar
        SabineOussi
        Skilled Sharer
        10 years ago

        Hi Anonymous,

         

        That's what I already did to sort them chronologically from oldest to newest date but I was searching for a way to sort the values within the table.

        I know that I can sort the total in a matrix but the total does not always reflect the same order as the values it's summing.

         

        For my case, I would like to achieve the following sorting:

        - if sorted by Feb (largest to smallest): customer 5, 3, 1, 2, 4

        - if sorted by Jan (largest to smallest): customer 5, 1, 3, 2, 4