topic Profitbase Income Statement Percentage in DAX Commands and Tips

Profitbase Income Statement Percentage

TimmK — Wed, 06 Jul 2022 09:20:41 GMT

I use the visual "Financial Reporting Matrix by Profitbase" to create an income statement with custom rows. The measures I use are Amount and %. The % is the amount of the specific year compared to the amount of the current year. For example, for sales the amount of 2022 is 100 and the amount of 2021 is 80. Thus, the amount of 2022 is 25% higher than the amount of 2021.

Unfortunately, as you can see below in the "Actual" table the % values are added in the custom row "Total Operating Income" instead of calculating the correct % as seen in the "Desired" table. How can I achieve the correct % values?

I want that it is still possible that I can use the year slicer, for example to add the years 2019 and 2018 or to remove years.

Re: Profitbase Income Statement Percentage

amitchandak — Wed, 06 Jul 2022 10:19:56 GMT

TimmK , Can you share % formula

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Profitbase Income Statement Percentage

TimmK — Wed, 06 Jul 2022 10:30:51 GMT

Yes, here is the % measure:

% =
DIVIDE(
CALCULATE(
[Sales],
ALL('Date'[Date].[Year]),
'Date'[Year]=YEAR(TODAY())
),
[Sales],
0

)

-1

Re: Profitbase Income Statement Percentage

Anonymous — Tue, 12 Jul 2022 06:08:00 GMT

HI TimmK,

You can try to use the following measure formula if helps:

% = VAR currDate = MAX ( 'Date'[Date] ) RETURN DIVIDE ( CALCULATE ( [Sales], FILTER ( ALLSELECTED ( Table ), 'Table'[Year] = YEAR ( currDate ) ) ), [Sales], 0 ) - 1

If that also doesn't help, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Profitbase Income Statement Percentage

TimmK — Tue, 12 Jul 2022 12:59:05 GMT

Hi Anonymous,

The formula unfortunately did not help.

Here is a PBIX with sample data:

https://drive.google.com/file/d/1aCo7CueMm-9vUO2CYXnnNCnnV8FRdywX/view?usp=sharing