cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Erwin
Helper II
Helper II

Issue with dynamic banding in PBI

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 tableBands 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 levelBehavior 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 levelBehavior 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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

 

Untitled.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

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:

 

Untitled.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix,

 

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

 

Thanks a lot for your response.

 

Regards,

Erwin

Phil_Seamark
Microsoft
Microsoft

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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 requirementAged 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 Smiley Happy

 

Regards,

 

Erwin

Erwin
Helper II
Helper II

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 tableBands 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 levelBehavior 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 levelBehavior 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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.