Profit and loss statement with account change during the year

[Anonymous]

Hi folks,

I have a problem once again, sorry for that!

I have created a P&L statement based on the video of Matt Allington.

Video: https://exceleratorbi.com.au/build-a-pl-with-power-bi/

The P&L statement wasn´t that difficult to create and works perfectly.

To show my P&L statement I use matrix visual to display years side by side. But now, i have problem, which isn´t so easy for me to solve. From year to another an account moved to another category. For example Account 860700 belongs to Revenues in 2017 and in 2018 it moved to Other Operating Income. The value is displayed in 2017 under Revenues and Other Operatin Income, i.e. twice.  It just should be shown once under Revenues.

I have a Excel Table which contains all accounts. I have added a column with year to assign account to the appropriate year. In 2017 account 860700 belongs to Revenues and in 2018 to Other Operatin Income.

Unfortunately it doesn´t work.

Does anybody has an idea how to solve it?

Thanks for all the answers I get!

freiburgc

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to create a dimension table with the Year, Type, Account and relate this with the other tree tables similar to the example below:

As you can see in the table account 1 changes from one type of account to the other on the next year

The example is not the same has you certainly have, but just by adding the additional table you can get expected result. In principle you can still keep everything in place has you have since this table is only working has a connection between the accounts, type of accoutn and year.

[Anonymous]

Hi  @MFelix ,

thanks a lot for your answer. I tried to adapt your solution to my Report, but wasn´t able to do it.

I don´t know what the problem is. Maybe you can have a look at my datas. That would be fine move. https://www.dropbox.com

/s/9m2z9pmzaju5iiv/GuV_V1.2_test.pbix?dl=0 

Thanks a lot again!

freiburgc

[MFelix]

Hi @Anonymous ,

What is the account that changed category?

Also can you tell me what is the table that I should check for that change? I think is the table : Hilfstab_Sachkto_Beziehungen

[Anonymous]

Hi @MFelix  ,

sorry for my bad description of my problem on my latest post. I was bit in a hurry yesterday...

As I already described, my P&L Statement is based on the video of Matt Allington.

Based on the video my data model looks like:

TAb_KopfzeileGUV_nichtNAV (2) is connected to Hilfstab_Kontozuordnung(2) with the key Kopf and Kategorie_Ebene_1

Hilfstab_kontozuordnung(2) is connected to Tab_Sachposten(G/L Entry) with the key Nr. and G/L Account No_

I have a couple of measure I use:

1. Sums the values of Amount

SummeBeträge = SUM('Tab_Sachposten (G/L Entry)'[Amount])

2.

Summierte_Werte_Tab2 = SUMX(VALUES('Hilfstab_Kontozuordnung (2)'[Multiplikator]),[SummeBeträge]*'Hilfstab_Kontozuordnung (2)'[Multiplikator])

3.

Berichtswerte_Tab2 = SUMX(VALUES('Hilfstab_Kontozuordnung (2)'[Multiplikator_Bericht]),[SummeBeträge]*'Hilfstab_Kontozuordnung (2)'[Multiplikator_Bericht])

4.

Zauberzahl_Tab2 = VAR IstEbene2Gefiltert=ISFILTERED('Hilfstab_Kontozuordnung (2)'[Kategorie_Ebene_2])
                 VAR IstKopfGefiltert=ISFILTERED('Tab_KopfzeilenGUV_nichtNAV (2)'[Kopf])
                 VAR IstEntwederGefiltert=OR(IstEbene2Gefiltert,IstKopfGefiltert)
                 VAR Ergebnis=IF(IstEntwederGefiltert, [Berichtswerte_Tab2],[Summierte_Werte_Tab2])
                 RETURN Ergebnis

I tried to reconstruct your data model with my tables, but I didn´t make it work.

Hopefully I could make more clear my Test-Data-Set.

Thanks for your support!

freiburgc

[MFelix]

Hi @Anonymous ,

I understand what you setup buty can you please tell me what is the account that change categorie so I can filter out the table and check what is incorrect?

Thank you.

[Anonymous]

Hi @MFelix ,

the account is 0860700.

thanks

freiburgc

[Anonymous]

Hi @MFelix ,

found my mistake. Had a big spin in my datasets and had to correct some cross filter.

Now your my P&L statement works the way I need it.

Sorry for my questions and thanks alot for your support!!!!!!

freiburgc

[MFelix]

Hi @Anonymous ,

Sorry I could not check the account but glad you were abble to figure it out.