Forum Discussion
Financial Report
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
What do you mean by: "You should not use dates at all, but instead join your column names column to the month-year column in calendar." That's my two tables:
THIS GOES UNTIL 31/12/2020
(The date goes until 31/12/2020)
Can you describe the join that you want me to make? (inner,left, on what?)
I'm not sure what you mean by "that's my 2 tables". What I would expect to solve this problem is
1. GL table containing transactions and a date column
2. A calendar table containing a date primary key and at least a mmm-yyyy column - this is the smallest level of granularity of time in your report
3. A header table as I have described before. The primary key is the name of the columns in your report, and you need a way to map to the calendar table too, so the description for months should be month/year, like mmm-yyyy. You can also have a mmm and yyyy column too if you want.
join the date of 1 to the date of 2
join the mmm-yyyy of 2 to the desc of 3.
not every description in 3 will have a matching record in 2. These are the items where you need to write a switch measure to display the value you need, as covered in my video.