Forum Discussion
Financial Report
Ok, what do you mean you have built the columns for 2020. How did you build them? What is different to these and the others you need?
I made a matrix visualization, I entered with hierarchy the accounts and subaccounts in the rows and then from a date table I added as columns the months and quarters, so every column that has to do with 2020 is done. (months,quarters,totals)
The problem is that I need on the same dashboard some more columns
1) difference between the current and previous month
2) diffenence between current (2020) and same month 2019
3) cumulative same month 2019
4) ONLY quarters for the 2019
Let me know if you need more explanations 🙂 Thanks
Ok. The way to solve this is to use a header table. It's the same principle I showed in the video you have already watched, but you need to do it for your calendar table too. You literally build a table with the columns you want to see. Add a sort column too, so you can control the order they appear on screen. Join it to your calendar table (you may need a mmm-yyyy column in both) and add the display column to the matrix columns. When the data is monthly data, you will see the numbers. When the data is something like "chg vs prior year", you will see blank. Then you need to write a switch measure that detects which column is displayed. If it is a month, then just show the total. If it is anything else, then show a different measure.
I wasn't able to follow your instructions, sorry. Can you give me more info on the calendar table that you mention. I tried to make one and put a sort column, but what about the key(id) ? It has to be unique in order to join it with other tables (i.e. general ledger)
Thanks.
I'm actually in process of writing a blog - not sure when it will Ben done, but if I get a run at it then it will be this week. You should not use dates at all, but I stead join your column names column to the month-year column in calendar. 1-4 won't find a match but 5-7 will, etc. Then you use the principle of header tables that I cover in my video to write a measure that returns different measures for 1-4 but the monthly total for 5-7, etc