Monthly Comparison

[SabineOussi]

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 use the matrix visual, it assigns a column to each date and time.
While I only need two columns grouping all activities within the month.

How can I achieve this?

Thanks.

[austinsense · Accepted solution]

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]

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]

And you obtain this:

#I'M Not An Expert#

[SabineOussi]

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

[Anonymous]

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