Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
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.
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.
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.
@kjrudy14 , do you want an aging bucket as a dimension/filter?
That needs dynamic segmentation with a group by says Invoice no. And independent bucket table
refer
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k