Forum Discussion
Accounts Report Measures
Hi,
I am developing a Accounts Receivables report for a client, but I am confused of making some Measures:
1) Count of Late Invoices (This year) (Card Visual)
2) Late Payment Amount (This year) (Card Visual)
3) Top 10 late customers by ( Over Due dates ) (Table Visual)
4) Due Payments ( this Week) (Card)
My Model is:
1) Receivables Table ( InvoiceNo, InvoiceDate, DueDate, PaymentDate, AmountReceivables, OverDueDays)
2) Calendar Table (Day,Date,WeekNo,Month,Year,MonthNo)
3) Customer Table (Id, Name)
Any Help in these Measures? amitchandak
3 Replies
MH3 , Your Calendar Table should join with all dates. Based on the requirement you can active join using userelationship
example count of invoice
Invoiced = CALCULATE(count(Receivables [Invoice No], userelationship('Calendar '[Date], Receivables [Invoice Date]))
Due= CALCULATE(count(Receivables [Invoice No], userelationship('Calendar '[Date], Receivables [due Date]))
paid = CALCULATE(count(Receivables [Invoice No], userelationship('Calendar '[Date], Receivables [payment Date]))@
late = calculate([Invoiced], filter(Receivables , (isblank([payment Date]) || [payment Date] >= [Due Date]))
YTD Sales = CALCULATE([late],DATESYTD('Calendar '[Date],"12/31"))
Now you can use time intelligence
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Hi amitchandak
your measures are right, but I didn't want this for my solution
actually, I want
(1) Count for (Count of late invoices ( this year )) to be displayed in (Card Visual) (will it be in context with OverDueDays, I have this column too)
(2) Late Payment Amount ( This year) to be displayed in a (card visual) (I need a amount that I receieved in last)(3) Count of Due Invoices (This Week) to be displayed in (Card Visual)
Now, I need to have these measures?
Hi, MH3
amitchandak gave you a lot of advice for reference. It's not easy to imagine data out of thin air.
If you're having trouble writing the code, please describe it in detail. Or you can share some sample data and your desired result.
Reference:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards,
Community Support Team _ Janey
