Forum Discussion
Financial Report
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.
Update! Here are the tables and relations that I created
Unfortunately, I expected that the columns that contain the 2020 data would be filled in but the whole report is empty.
Any idea? Thanks!
Update Vol2
Rookie mistake, I had to turn into "Text" the date 'mmm yyyy' column
I will come back here if I have new questions about the measures! Thanks a lot!
Hello MattAllington
I tried to do a measure but didn't workout, is there a way to caclulate let's say the column "Q1 2019 Total"?
The idea is that I want a sum of same account numbers WHERE Quarter = 1, and the same logic I think goes to the other columns, too, what is the DAX strategy/measure that I should follow?