Issue with dynamic banding in PBI

[Erwin]

Hi all,

For a client I'm trying to apply banding to their open invoice amounts per reporting date. Additionally this client does not only want banding applied to the open amount at the current reporting date, but also wants to be able to create an historic overview.

I started out with creating a measure called "Amt of sent" to establish the amount of sent invoices during the reporting period:

Amt of Sent =
CALCULATE (
   SUM ( FactTable[Amount] ),
   USERELATIONSHIP ( FactTable[Baseline date], 'Calendar'[Date] )
)

Secondly I created a measure called "Amt of Open" to calculate the open amount per reporting date:

Amt of Open =
CALCULATE (
   [Amt of Sent],
   FILTER (
       FactTable,
       OR (
           ISBLANK ( FactTable[Clearing date] ) = TRUE (),
           FactTable[Clearing date] >= MAX ( 'Calendar'[Date] )
       )
   )
)

Thirdly I created a measure called "Amt of Open RT" to calculate the running total amount of open invoices per reporting date:

Amt of Open RT =
CALCULATE (
   [Amt of Open],
   FILTER (
       ALL ( 'Calendar'[Date] ),
       'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
   )
)

Until this point I encountered no problems. The above measures work as expected and required by the client.

The problem arises when I create a measure to apply banding according to below table:

Bands table:

Bands table

Measure:

Amt of Open RT Banding =
IF (
   COUNTROWS ( Bands ) = 1,
   CALCULATE (
       [Amt of Open RT],
       FILTER (
           'Calendar',
           MAX ( 'Calendar'[Date] ) - MAX(FactTable[Due date])
               >= MAX ( Bands[Min] )
               && MAX ( 'Calendar'[Date] ) - MAX(FactTable[Due date])
                   < MAX ( Bands[Max] )
       )
   ),
   [Amt of Open RT]
)

The behavior of this last measure "Amt of Open RT Banding" is understandable but not what I want to see. The measure works correctly when applied to the lowest level of data in my Fact table, which is invoice level.

Behavior at invoice level

I've highlighted three invoices, all belonging to project 9944. I want to take these as an example. Of the three invoices, invoice 108159 has a due date of 17-01-2016, which is 14 days prior to the reporting date (31-01-2016). Based on this value this invoice is categorices as "5-30 days" (overdue). Both other invoices (124110 and 130112) have a due date after the reporting date (01-02-2016 and 06-02-2016), so these are categorized as "Current". My conclusion is that, on the invoice level, the measure is working correctly.

When I create an overview at an aggregated level the behavior of the "Amt of Open RT Banding" shows something different.

Behavior at aggregated level

Here you can see that for the highlighted project 9944 (our example project), the total amount of three open invoices is put into the "Current" bucket. This is not correct, because we already established that invoice 108159 should fall into the "5-30 days" band. The correct behavior would be:

Current        440

5-30 days    200

Total            640

Now I think I understand why there is a difference in behavior between invoice level and aggregated level. It's because, at the invoice level, each individual due date is evaluated, while at agregated level only the highest due date of the three invoices is evaluated. The issue is therefore related to context.

My question is: How can I create a measure that evaluates each indivdual invoice, regardless of aggregated or non-aggregated context?

Many thanks in advance for your reply.

Regards,

Erwin

[MFelix · Accepted solution]

Hi @Erwin,

First of all my solution is based on this post by @marcorusso and @AlbertoFerrari.

I believe tha your problem is related with the way the measures in calculated in the context of your data, you have a project that as several invoices and you want to detail the due ages by groups, however when using the max functions on your Due Date when you do the group by project you will get the highest date of all invoices that's why you have a result on project level (9944) Curent and not 5-3 and Current.

Createw the following measures:

Age_Calculation = INT ( MAX ( Calendar[Date] ) -  MAX ( FactTable[Due Date]) ) 


Amt of Open RT Banding =
IF (
    ISFILTERED ( Bands[Description] ),
    CALCULATE (
        [Amt of Open],
        FILTER (
            VALUES ( FactTable[Document number] ),
            COUNTROWS (
                FILTER (
                    Bands,
                    [Age_Calculation]  >= Bands[Min]
                    && [Age_Calculation]  < Bands[Max]
                )
            ) > 0
        )
    ),
    [Amt of Open]
)

The result is below wityh the detail of invoice and at project level:

Regards,

MFelix

[Erwin]

Hi MFelix,

Brilliant!! This is exactly what I need for my client

Thanks a lot for your response.

Regards,

Erwin

[Phil_Seamark]

HI @Erwin

Why not create a calculated column that puts each invoice into the appropriate band in the table level, then use that?

Will probably speed your report up too.  I take it each indivual line can have only one value - or can this change?

[Erwin]

Hi Phil,

I think I understand what you mean. But my problem with that is that (as far as I know) using a calculated column to categorize each open invoice amount into the appropriate band means that you can only do that for one reporting date.

What I need is the ability to categorize each open invoice amount into the appropriate band at any given reporting date.

Please review below picture showing the client's requirement:

Aged debt requirement

As you can see in the above picture the client's requirement is to show the banding of open amounts for 12 different reporting dates (Jan=31/1, Feb=28/2, etc, etc). What I know about using a calculated column in a banding scenario is that it will give you banding, but only for one specific reporting date. This explains my intention to use a measure instead of a calculated column.

My thanks for your reply though

Regards,

Erwin

[Erwin]

Hi all,

For a client I'm trying to apply banding to their open invoice amounts per reporting date. Additionally this client does not only want banding applied to the open amount at the current reporting date, but also wants to be able to create an historic overview.

I started out with creating a measure called "Amt of sent" to establish the amount of sent invoices during the reporting period:

Amt of Sent = 
CALCULATE (
    SUM ( FactTable[Amount] ),
    USERELATIONSHIP ( FactTable[Baseline date], 'Calendar'[Date] )
)

Secondly I created a measure called "Amt of Open" to calculate the open amount per reporting date:

Amt of Open = 
CALCULATE (
    [Amt of Sent],
    FILTER (
        FactTable,
        OR (
            ISBLANK ( FactTable[Clearing date] ) = TRUE (),
            FactTable[Clearing date] >= MAX ( 'Calendar'[Date] )
        )
    )
)

Thirdly I created a measure called "Amt of Open RT" to calculate the running total amount of open invoices per reporting date:

Amt of Open RT = 
CALCULATE (
    [Amt of Open],
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Until this point I encountered no problems. The above measures work as expected and required by the client.

The problem arises when I create a measure to apply banding according to below table:

Bands table

Measure:

Amt of Open RT Banding = 
IF (
    COUNTROWS ( Bands ) = 1,
    CALCULATE (
        [Amt of Open RT],
        FILTER (
            'Calendar',
            MAX ( 'Calendar'[Date] ) - MAX(FactTable[Due date])
                >= MAX ( Bands[Min] )
                && MAX ( 'Calendar'[Date] ) - MAX(FactTable[Due date])
                    < MAX ( Bands[Max] )
        )
    ),
    [Amt of Open RT]
)

The behavior of this last measure "Amt of Open RT Banding" is understandable but not what I want to see. The measure works correctly when applied to the lowest level of data in my Fact table, which is invoice level.

Behavior at lowest level

I've highlighted three invoices, all belonging to project 9944. I want to take these as an example. Of the three invoices, invoice 108159 has a due date of 17-01-2016, which is 14 days prior to the reporting date (31-01-2016). Based on this value this invoice is categorices as "5-30 days" (overdue). Both other invoices (124110 and 130112) have a due date after the reporting date (01-02-2016 and 06-02-2016), so these are categorized as "Current". My conclusion is that, on the invoice level, the measure is working correctly.

When I create an overview at an aggregated level the behavior of the "Amt of Open RT Banding" shows something different.

Behavior at aggregated level

Here you can see that for the highlighted project 9944 (our example project), the total amount of three open invoices is put into the "Current" bucket. This is not correct, because we already established that invoice 108159 should fall into the "5-30 days" band. The correct behavior would be:

Current        440

5-30 days    200

Total            640

Now I think I understand why there is a difference in behavior between invoice level and aggregated level. It's because, at the invoice level, each individual due date is evaluated, while at agregated level only the highest due date of the three invoices is evaluated. The issue is therefore related to context.

My question is: How can I create a measure that evaluates each indivdual invoice, regardless of aggregated or non-aggregated context?

Many thanks in advance for your reply.

Regards,

Erwin