Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I looking to create as of aging report, but have difficulty on getting on the age bucket based on as of date.
Example, when select Oct 2017, age bucket will show last 30, 60, and 90 days from Oct 2017. When select July, is from July. -30 days, -60days, -90 days.
Please assist.
Thanks
Hi @marcuschuah,
You need a date table (suppose it's named as 'DimDate'), and add [date] column from this date table into slicer. You could add such a filter in your calculation:
Result=CALCULATE(expression, FILTER(TableName, TableName[Date]>=DateAdd(MAX(DimDate[date]),-60,day) && TableName[Date]<=DateAdd(MAX(DimDate[date]),-30,day) ))
For more advice, please provide sample data.
Regards,
Yuliana Gu
Hi @v-yulgu-msft,
Should it be to calculate the age 1st? Like how to get 1-30 days & 31-60 days? As the entries date will be define depend on month selection. The month will check the aging bucket.
My table consist of
Transaction & DueDate Customer Type Amount
1/9/2017 PowerBI Invoice 1000
2/9/2017 PowerBI Payment -1000
1/10/2017 PowerBI Invoice 2000
1/11/2017 PowerBI Invoice 1000
Expected to return
If i run the select as of date of end of Septemeber
Customer 1-30 31-60 Not Due
PowerBI 0 0 3000
If on end of Oct
Customer 1-30 31-60 Not Due
PowerBI 2000 1000
If on end of Nov
Customer 1-30 31-60 Not Due
PowerBI 1000 2000 0
Assuming you have a column for dates, try creating the following columns:
Last 30 days= DateAdd(Date,-30,day)
Last 60 days= DateAdd(Date,-60,day)
Last 90 days= DateAdd(Date,-90,day)
Using the formula, last 60 days will also return 30 days record, same for 90 days. The bucket should be view as
1-30days | 31 Days - 60 days | 61 days - 90 days.
Before adding the bucket, i think need to calculate which line is refer to the bucket?
for example;
Date Name Amount
23/10/2017 Marcus $100
If I run as of September. No record will show.
If run on Nov, $100 will be on 1-30days
If run on Dec, $100 will be on 31-days
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |