Power BI Overdue Amounts Bar Chart

Problem Analysis

To display overdue amounts in 10-day buckets on a bar chart in Power BI, the following steps need to be taken:

1. Calculate the number of days each payment is overdue.
2. Group the overdue amounts into 10-day buckets.
3. Visualize the total overdue amount for each 10-day bucket on a bar chart.

Strategic Solution Development

1. Calculate Overdue Days: Create a calculated column to calculate the number of days each payment is overdue using DAX functions like TODAY() and DATEDIFF().
2. Group into 10-day Buckets: Create a new column to group the overdue amounts into 10-day buckets using a combination of SWITCH() and ROUNDUP() DAX functions.
3. Visualize on Bar Chart: Use the new 10-day bucket column to visualize the total overdue amount for each bucket on a bar chart.

Comprehensive Advice

1. Calculate Overdue Days: Create a calculated column using the DAX formula:

   Overdue Days = DATEDIFF([Due Date], TODAY(), DAY)
   

2. Group into 10-day Buckets: Create a new calculated column:

   Overdue Bucket = 
   SWITCH( 
      TRUE(),
      [Overdue Days] <= 10, "0-10 days",
      [Overdue Days] <= 20, "11-20 days",
      [Overdue Days] <= 30, "21-30 days",
      "Over 30 days"
   )
   

3. Visualize on Bar Chart:

   • Add a bar chart to the report canvas.
   • Place the Overdue Bucket column in the Axis field and the sum of overdue amounts in the Values field.

Code-Based Solution

Overdue Days = DATEDIFF([Due Date], TODAY(), DAY)

Overdue Bucket = 
   SWITCH( 
      TRUE(),
      [Overdue Days] <= 10, "0-10 days",
      [Overdue Days] <= 20, "11-20 days",
      [Overdue Days] <= 30, "21-30 days",
      "Over 30 days"
   )

Detailed Explanations

• Utilize DAX to calculate the number of days each payment is overdue and to group the overdue amounts into 10-day buckets.
• Create a bar chart to visualize the total overdue amount for each 10-day bucket.
• The SWITCH function is used to categorize the overdue days into specific buckets.

By following these steps, you can effectively display overdue amounts in 10-day buckets on a bar chart in Power BI.