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 all,
I am a relatively new user in Power BI and am needing assistance with the following requirement.
My client is running on Dynamics 365 and would want to replicate The Vendor aging Report in Power BI and i am connecting to the client using OData.
I followed this link for reference https://community.powerbi.com/t5/Desktop/Accounts-Receivable-Aging-Report/td-p/106367
And i have created a measure which calculates the transactions age using the due date and by using this measure i am trying to calculate the amounts for
-All transactions which fall under column 0-30Days
-All transactions which fall under column 31- 60Days
-All transactions in which fall under column 61- 90Days
-All transactions which fall over column +90 days
-Transaction for the current date
I am using this formula to create calculated columns for the respective periods
And the current date should be the date which is selected from the slicer, like if i choose 3rd nov 2017 in slicer, it should be the currenr date and i should get the amounts according to that but the measure which i used to calculate the transactions age is not giving me the correct values as you can see in the picture, all the values are coming in 90+ period.
This is the measure which is used -
Hi @deeksha,
You are using a calculated column which is computed during the database process and then stored in the model which will ony update upon dataset refresh or, of course, when its formula or reference columns have been modified (more about calculated columns vs measures at https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/). That being said, the age colum that has already been stored in the model will not update when you select anything in the slicer so use a measure instead.
In the sample pbix, I created a disconnected (no relationship to fact) calculated table which will hold the age and the corresponding buckets.
AgingBucket =
VAR __BASE =
SELECTCOLUMNS (
GENERATESERIES ( 0, DISTINCTCOUNT ( 'Fact'[Due Date] ), 1 ),
"Age", [Value]
)
RETURN
ADDCOLUMNS (
__BASE,
"Bucket", SWITCH (
TRUE (),
[Age] > 91, "90+",
[Age] > 60, "61 to 90",
[Age] > 30, "31 to 60",
[Age] > 0, "1 to 30",
" Current"
)
)
I would then create a measure to for the invoice age and another one for the amount by age
Invoice Age =
DATEDIFF (
SELECTEDVALUE ( 'Fact'[Due Date] ),
CALCULATE ( MAX ( 'Fact'[Due Date] ), ALLSELECTED ( 'Fact' ) ),
DAY
)
Invoice Amount by Age =
CALCULATE (
[Sum of Amount],
FILTER (
'Fact',
COUNTROWS (
FILTER (
AgingBucket,
[Invoice Age] >= AgingBucket[Age]
&& [Invoice Age] <= AgingBucket[Age]
)
) > 0
)
)
Since Aging Bucket columns and Invoice Age measure has no relationship, you will end up seeing a weird result if you put them together in a visual. Create another measure to fix this and use this in your visual instead.
InvoiceAge =
IF ( NOT ( ISBLANK ( [Invoice Amount by Age] ) ), [Invoice Age] )
You may download the sample pbix here
https://drive.google.com/file/d/1U7QCOQafrAi9Qm3og0r2anStYe4KlKDk/view?usp=sharing
Proud to be a Super User!
@danextianHey, i am getting the amounts separately in 0-30,30-60, 60-90 and 90+ period but the disconnected table which we created by using formula
AgingBucket = VAR __BASE = SELECTCOLUMNS ( GENERATESERIES ( 0, DISTINCTCOUNT ( 'Fact'[Due Date] ), 1 ), "Age", [Value] ) RETURN ADDCOLUMNS ( __BASE, "Bucket", SWITCH ( TRUE (), [Age] > 91, "90+", [Age] > 60, "61 to 90", [Age] > 30, "31 to 60", [Age] > 0, "1 to 30", " Current" ) )
is calculating the right values only for the transdates which are available, i mean if i choose 13th sep 2017 in slicer, but there is no transaction on 13th sep, so the DISTINCTCOUNT function will choose the nearest date like 2nd august 2017 and will assign 1 to it, which make it to fall in 1-30 period but it should have come in 30-60, with an age of 41 days, how can i get that???
Hello @deeksha,
I have tried to recreate the scenario. I am not sure if this is what you are looking for.
You may find the solution pbix file here
Regards,
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
@vivran22Hey Thank you for the reply, but your sample report is working and evaluating the data in other way, its not how i want in my report,
here you can check my sample report data
Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
@danextian Thank you so much for your valuable answer, i have understood and created the disconnected table and measures according to you, but i need the sum of amount for 30 days in a seprate column and similarly for 60, 90 and 90+, so should i create separate measures for all like the invoice age? but for that do i have to change my bucket table too?
Hi @
You can try the formula below to compute for the invoice amount per bucket.
Invoice Amount - 0 to 30 days =
CALCULATE (
[Sum of Amount],
FILTER ( 'Fact', [Invoice Age] >= 0 && [Invoice Age] <= 30 )
)
Proud to be a Super User!
@danextian Hi,
Its not working, i have created 3 more like the invoice amount for 0-30,
To have datediff as measure you need yo have a level, So you need to use values or summarize
datediff =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(average(No of days = DATEDIFF(VendAgingGlobals[DueDate],_max,DAY)),values(VendAgingGlobals[OrderId]))
//or
averagex(values(VendAgingGlobals[OrderId]),DATEDIFF(VendAgingGlobals[DueDate],_max,DAY))
Put an IF on top of it to get values.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
@amitchandakThanks for the reply, but i don't have order id in my table, i only have accountNum, Voucher and lastsettleVoucher and in
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |