Profitbase Income Statement Percentage

[TimmK]

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.

[v-shex-msft]

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

[TimmK]

Hi @v-shex-msft,

The formula unfortunately did not help.

Here is a PBIX with sample data:

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

[amitchandak]

@TimmK , Can you share % formula

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

[TimmK]

Yes, here is the % measure:

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

)

-1