Comparison between two financial periods - Wrong values shown in the table

[pierpa]

Hello everyone, I have a problem with the comparison of two financial periods, I created few measures that works in combination with a filter. So, basically, the user can select two or more periods to compare through a smart filter, and then the data are shown in some tables in the report.

To do this i created few measures that I will explain here.

Actual

Actual = CALCULATE (

    SUM ( 'Base dati'[Importo] );

    FILTER ( ALL ( Periodi ); Periodi[Periodo] = MAX ( 'Base dati'[Periodo] )))

This measure takes the LAST period in the filter, summing up all the values in the column “importo” (amount).

Previous

Previous = CALCULATE (

    SUM ( 'Base dati'[Importo] );

    FILTER ( ALL ( Periodi ); Periodi[Periodo] = MIN ( 'Base dati'[Periodo] )))

This measure takes the OLDEST period in the filter, summing up all the values in the column “importo” (amount).

Then the measure Var. calculates the difference between the two periods.

Var. = [Actual]-[Previous]

The problem is that when I have a two periods in which some rows contains the values only in one of the column, PBI shows me the value repeted even in the row of the period without that value.

Example:

2017

Expenses	2017
Selling	100
General
Administrative	100
Total	200

2018

Expenses	2018
Selling	200
General	200
Administrative	200
Total	600

2017 vs 2018

Expenses	2017 (Previous)	2018 (Actual)
Selling	100	200
General	200	200
Administrative	100	200
Total	200	600

Instead it should be:

2017 vs 2018

Expenses	2017 (Previous)	2018 (Actual)
Selling	100	200
General	-	200
Administrative	100	200
Total	200	600

It is important to state that the total amount are correct, despite PBI showing values where they shouldn’t be.

Any suggestion?

Thank you in advance.

[Anonymous]

I think your problem stems from the way you define your filters. The filter on categories is still relevant inside your FILTER statement: if you only have values of the category "General" in 2018, MIN ('Base dati'[Periodo] ) will return 2018 as long as the row context gets you category="General". It might be enough to use CALCULATE ( MIN ('Base dati'[Periodo]), ALL ( 'Base dati'[Category] ) )* instead of the simple MIN statement; I didn't test that though. The same then holds true for the corresponding MAX statement.

*I assume your column is from 'Base dati' and is called "Category" as you didn't specify this.

[pierpa]

Hello @Anonymous  thank you for your explanation. I tried to use the formula you suggested but it doesn't work.

The example I posted is just for sake of simplicity, unfortunately the situation is far more complex. This report I'm trying to build is basically a financial statement of a company that I would like to replicate on PBI for analysis purpose.

Unfortunately data are not explicit for every category of the financial statement, so the row "general" I put on the example is, for instance, the SUM of a lot of specific ledgers, and this holds true for every information shown. I can not post here the PBI for obvious reasons, but I can try to explain things a little better. 

The whole report is built upon a data set of 300 000 rows that contains all the transactions amount. The amount are stored in the column "Importo" (amount). 

Every transactions is then associated to:

• a column "Periodo" (Period)
• different codes called "Dettaglio 1", "Dettaglio 2", "Dettaglio 3" (Detail 1, 2, 3) that are used to associate the transactions to specific categories
• an accounting code "Conto Co.Ge." for the same purpose of the details
• and other column to simplify construction of the raport. 

Period	Detail 1	Detail 2	Detail 3	Accounting Code	Amount
YE18	F56923	C85674	D84930	AB12345	100000

This is all contained into a single table called "Base dati" (database). 

Then, to categorize all the period we have a table called "Periodi" (periods) that contains:

• A column "Periodo" (period) with the name of the financial period, e.g. YE18 (Year End 2018)
• A column to order all this period called "Data" (Date), e.g. 31/12/2018

So when I use the formulas Actual and Previous, what I'm trying to do is to let the user select two financial periods in a smart filter and sum all the transactions for the visualization I have in a specific page of the report. 

Actual = CALCULATE (

SUM ( 'Base dati'[Importo] );

FILTER ( ALL ( Periodi ); Periodi[Periodo] = MAX ( 'Base dati'[Periodo] )))

So I sum all the database, and I filter it for the chosen periods selected in the smart filter.

Please consider that I started using PBI since last month so I'm very new to all this. 

Thank you in advance!

[Icey · Accepted solution]

Hi @pierpa ,

I create a simple example. Please check if it is what you want.

Actual = 
CALCULATE (
    SUM ( 'Base dati'[Amount] ),
    FILTER (
        ALLSELECTED ( 'Base dati'[Period] ),
        'Base dati'[Period]
            = MAXX ( ALLSELECTED ( Periodi[Period] ), 'Periodi'[Period] )
    )
)

Previous = 
CALCULATE (
    SUM ( 'Base dati'[Amount] ),
    FILTER (
        ALLSELECTED ( 'Base dati'[Period] ),
        'Base dati'[Period]
            = MINX ( ALLSELECTED ( 'Periodi'[Period] ), 'Periodi'[Period] )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pierpa]

Hello @Icey thank you for your support! Really appreciated. 

The formula you suggested works as expected, but please, can you explain me what have you done? I would like to understand the differences between what I've done and that you fixed. 

Thank you again.

[Icey]

Hi @pierpa ,

For the meaning of ALLSELECTED, you can get details in these blogs:

The definitive guide to ALLSELECTED;

Understanding ALLSELECTED.

The below expression is to filter table "Base dati" based on selected 'Base dati'[Period], which is affected by 'Periodi'[Period] for the relationship.

FILTER (
        ALLSELECTED ( 'Base dati'[Period] ),

The below expression is to get the Latest Period in the periods you selected.

MAXX ( ALLSELECTED ( Periodi[Period] ), 'Periodi'[Period] )

Best Regards,

Icey

[pierpa]

Thank you!

[Anonymous]

As an afterthought, it is important to notice that using MIN/MAX in any way like this might return unexpected results; you've seen one of those yourself. Even with an outer CALCULATE like I wrote above the the returned values might not be what you expect: if for example you have no values for a selected (min-) period, the results of the next period that does have values will instead be shown (just like the 200 in your example) without this necesserily being obvious to the user. If this can't happen with your data, there is no problem, but if this is possible you might want to build your report in a different way.