Forum Discussion
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 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.
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.
5 Replies
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.
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.
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..
