Create the customer aging report the aging calculation base on the user selected date by the slicer

[BIlal3]

Hello,

I'm working on a customer aging report in Power BI where the aging calculations are based on a user-selected date from a slicer. The table visualization displays the correct calculations, but the matrix visualization is showing incorrect results. I've been troubleshooting this issue for the past five days without success.

Could you please help me resolve this? For clarity, I've attached the PBIX file.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[BIlal3]

Please find the link for PBIX File: https://drive.google.com/file/d/1_1sMFC1c5v2uyTvUbp_dhoB3chFNn7Uq/view?usp=sharing

[Irwan]

hello @BIlal3 

i dont see any problem with either table or matrix.

C789 only have "101-200 Days" value in 'Bucket_CAL' with Sum of doctoral is 1260.

Both matrix and table show exact same value.

i might be misunderstood your issue but looks like to me that you want to have the matrix column value (matrix header) changed depend on the date slicer therefor you compare your 'Bucket_CAL' in matrix visual with your 'Bucket' in table visual.

'Bucket' as measure will have vary of values depend on your date slicer while 'Bucket_CAL' as column will have single value (this means that your matrix column will stay same regardless where date slicer is placed).

Hope this will help.

Thank you.

[BIlal3]

Thank you for the reply. I’m new to Power BI, and this is my first post, so I may not have explained my issue clearly.

I need to create a bar chart with the following setup:

• X-axis: Buckets such as 0-30 days, 31-60 days, 61-90 days, etc.
• Y-axis: The sum of DocTotal, based on the date selected by the slicer.

For example, if I select the date 01-08-2024 in the slicer, the measure should display the correct values, which I have already verified with the SAP Business One application.

Problem 1:

• I am unable to drop the Bucket measure into the X-axis. Power BI does not allow me to do.

Problem 2:

• As a workaround, I created a calculated column called Bucket_cal, which Power BI does allow me to drop into the X-axis, but the calculations are incorrect. Here’s the DAX formula I used: Days_cal = DATEDIFF(SELECTEDVALUE(OINV[docdate]), DateTable[datecalmax], DAY)
  how DAX formula is work’s invoice date, max date of my datetable           

Example:

Doc. No.	Posting Date	DateTable[datecalmax]	Amount	0 - 30	31 - 60	61 - 90	100 - 200
1317	17.08.24	31.12.24	GBP  240.00				GBP  240.00
1318	17.07.24	31.12.24	GBP  120.00				GBP  120.00
1319	06.08.24	31.12.24	GBP  180.00				GBP  180.00
1320	13.07.24	31.12.24	GBP  300.00				GBP  300.00
1321	18.06.24	31.12.24	GBP  240.00				GBP  240.00
1322	03.07.24	31.12.24	GBP  180.00				GBP  180.00

I also have the actual calculations from the SAP application based on two dates:

1. 01-08-2024

2- 17-08-2024

I hope this helps clarify my problem. If you need more information, please let me know, and thanks again!

[gmsamborn · Accepted solution]

Hi @BIlal3 

I created a Buckets table like this:

Then a single measure:

Bucket Amt = 
VAR _AgingDate = MAX( 'Date'[Date] )
VAR _Lower = SELECTEDVALUE( 'Buckets'[lower] )
VAR _Upper = SELECTEDVALUE( 'Buckets'[Upper] )
VAR _Table1 =
    FILTER(
        ADDCOLUMNS(
            'OINV',
            "__Days",
                INT( _AgingDate - [docdate] )
        ),
        [__Days] >= _Lower
            && [__Days] <= _Upper
    )
VAR _Result = 
    SUMX(
        _Table1,
        [doctotal]
    )
RETURN
    _Result

Let me know if you have any questions.

Dynamic Aging.pbix

[BernatAgulloMVP]

This looks good to me