Forum Discussion

kjrudy14's avatar
kjrudy14
Frequent Visitor
4 years ago

Dynamic Date in AR Aging Report

Hello,

 

I am currently working on an AR Aging report in Power BI.  I have the report completed, however, I am unable to make the aging buckets dynamic with a date selector.    The data is only as of Today's date...

 

Here are a few of the calculations :

 

Days Aged = INT(max('Calendar Table'[Date])-max('AR Data'[Due Date]))    -->Calculates Days Aged
 
Calendar Table = CALENDAR(min('AR Data'[Due Date]),today())       -->Calendar Table 
 
Age 1-30 = CALCULATE(sum('AR Data'[Remaining Balance]), filter(ALL('AR Data'[Remaining Balance]), [Days Aged] >= 1 && [Days Aged] < 31))       -->Aging Band 1-30
 
Age 31-60 = CALCULATE(sum('AR Data'[Remaining Balance]), filter(all('AR Data'[Remaining Balance]), [Days Aged] > 30 && [Days Aged] < 61))    -->Aging Band 31-60 
 
The rest of the bands follow the same logic...
 
Any advice?
 
 
 
 
Need Help

4 Replies

  • kjrudy14's avatar
    kjrudy14
    Frequent Visitor
    4 years ago

    Hello,

     

    The report is currently laid out like below by Customer :

     

     

    I just want to be able to add a date slicer and look at the aging report as of any particular date.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi kjrudy14 

    Could you share some sample data or your sample file?  So that I can write specific measure for your sample file.

    -
    1. create a measure to get the selected date in slcier, 

    selected_date= SELECTEDVALUE('Calendar Table'[Date])

    2. then get the calculating date period by using the selected date, 

    e.g. var _startDate= selected_date-31

    3. calculate sum

    Age 1-30 = CALCULATE(sum('AR Data'[Remaining Balance]), filter(ALL('AR Data'[Remaining Balance]), your date period))

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • faisalazh's avatar
    faisalazh
    Icon for Advocate II rankAdvocate II
    4 years ago

    I think you have to create a summary table to cross join your calendar and aging group. then create column to summarize the amounts then create relationship between new summary table with calendar.

    1. cross join your calendar with aging group you can do formula like:

    Summary AR = CrossJoin('CalendarTable', 'Aging Group Table')

    2. Create in New Summary AR table a calculated columns. eg. Age 1-30, etc.

    3. Create relationship 'Summary AR' table with 'CalendarTable'.

    hope this help.