Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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...
Tips and Tricks
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    Hi Anonymous 

     

    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

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
gmsamborn
Icon for Super User rankSuper User
2 years ago

Hi Anonymous 

 

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's avatar
    BernatAgulloMVP
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    This looks good to me