Forum Discussion

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

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

gmsamborn

Super 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