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 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.

 

 

 

 

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

     

6 Replies

    • Irwan's avatar
      Irwan
      Icon for Super User rankSuper User
      2 years ago

      hello Anonymous 

       

      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.

      • 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!