Financial Report

[Anonymous]

Hello,

I'm trying to build a financial report that looks like the picture. Until now I have managed to, build the 17 columns that have to do with the current year 2020. (months,quarters and 12-month total). I'm really struggling to make the rest of columns and add them to the matrix. Any ideas? My data set looks like the pic below.

[MattAllington]

You may find me demo here helpful. 
https://exceleratorbi.com.au/build-a-pl-with-power-bi/

[Anonymous]

Fun fact I saw that video before making this post.

My dashboard is more complicated so it didn't help me 😕

Thanks a lot tho!

[MattAllington]

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?

[Anonymous]

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

[MattAllington]

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. 

[Anonymous]

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.

[MattAllington]

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

[Anonymous]

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?)

[MattAllington]

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. 

[Anonymous]

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!

[Anonymous]

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!

[Anonymous]

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?