Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am tring to do a account receivable report, which I cannot find a way to do it.
the biggest issue is I cannot find a way to do the measure that needs calculate 2 tables value and use the slicer value.
please see the detail below, I have a invoice table, and i need measure the Remainingamount that use the calculator
'InvoiceTable'[Amount] - sum('paymentTable'[amount], where the payment date is earlier than the slicer date)
Measure to take the slicer date is use the before slicer : MeasureSelectedeDate:= CALCULATE(
SUM('Date'[DateKey]),
LASTDATE('Date'[DateKey])
)
Invoice table:
Remainingamount is measure , based on the time slicer on report UI
Invoice id | Amount | Invoicedate | Remainingamount |
1 | 10000 | 2019-1-1 |
|
2 | 20000 | 2019-1-2 |
|
Payment table
Invoice id | Amount | Paydate |
1 | 5000 | 2019-1-5 |
1 | 3000 | 2019-2-1 |
1 | 2000 | 2019-2-10 |
2 | 10000 | 2019-2-1 |
2 | 5000 | 2019-2-5 |
2 | 5000 | 2019-2-10 |
MeasureSelectedeDate = 2019-1-30 (slicer date)
Invoice id | Amount | Invoicedate | Remainingamount |
1 | 10000 | 2019-1-1 | 5000 |
2 | 20000 | 2019-1-2 | 20000 |
MeasureSelectedeDate = 2019-2-6(slicer date)
Invoice id | Amount | Invoicedate | Remainingamount |
1 | 10000 | 2019-1-1 | 2000 |
2 | 20000 | 2019-1-2 | 5000 |
MeasureSelectedeDate = 2019-2-11(slicer date)
Invoice id | Amount | Invoicedate | Remainingamount |
1 | 10000 | 2019-1-1 | 0 |
2 | 20000 | 2019-1-2 | 0 |
Is this doable?
Regards,
Gordon
Make sure your using date dimension , Try a formula like this
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(allselected(date),date[date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
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 Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi Amitchandak,
Thaks for quick reply.
however I can new to PowerBI and I cannot understand your sample code.
can you please use the 'Invoice' and 'Payment' table as sample.
as you can see in my sample
the 'Invoice'[RemainingAmount] is calculated based on
'Invoice'[Amount]
'Payment'[Amount]
'Payment'[Paydate]
Regards,
Make sure you have created a date dimension. The link I have given in the last mail. Join to respective dates payment and invoice
Payment remaining = CALCULATE(SUM('Invoice'[Amount]),filter(date,date[date] <=maxx(allselected(date),date[date])))
- CALCULATE(SUM('Payment'[Amount]),filter(date,date[date] <=maxx(allselected(date),date[date])))
Hi Amitchandak,
In your code, it seems like sum all 'Invoice'[Amount] - sum all 'Payment'[Amount]
but I need this for each invoice, so the sum('Payment'[Amount]' should filter by the 'Invoice'[InvoiceId] and the date
am I right?
Use invoice Id in visual and date filter as a slicer. This will take the row context when you use client/customer/vendor and Invoice id
No, the InvoiceId cannot put in visual.
as I need the remainingamount for each invoice
and I also need the remainingamount for each customer. which is summary for all invoce under that customer .
so in the slicer I will have
Customer slicer
Date slicer
I have no idea how to link the payment table to invoice table by invoice id and calculate the remainamount for each invoice.
You are not doing anything which makes it rollup from invoice level.
But to create invoice dim use distinct
distinct(Invoice[ID]) , Assume all invoices are there.
Or summarize(Invoice,Invoice[ID],Invoice[Date]) //Means all unique info of invoice
Try the invoice ID grouping
invoice due =CALCULATE(SUM('Invoice'[Amount]),filter(date,date[date] <=maxx(allselected(date),date[date])))
payment due = CALCULATE(SUM('Payment'[Amount]),filter(date,date[date] <=maxx(allselected(date),date[date])))
Payment remaining =
sumx(summarize(InvoiceDIM,invoiceDIM[ID], "_inv",[invoice due],"_pay",[payment due ]),[_inv]-[_pay])
Hi Amitchandak,
I tried this solution, however the performance seems to be terrible slow,
my invoice table has 38,000 rows
my payment table has 100000 rows
if I put the paymentremaing to pivot table or table , the power bi not load the data after 10 minutes.
table DimInvoice:=DISTINCT(Invoice[InvoiceId])
all 3 measure (InvoiceDue,PaymentDue,AccountReceivable are created at table DimInvoice)
InvoiceDue:=CALCULATE(SUM(Invoice[Amount]))
PaymentDue:=CALCULATE(SUM(Payment[Amount]),FILTER(Payment,Payment[TRANSDATE]<[MeasureSelectedeDate]))
AccountReceivable:=SUMX(
SUMMARIZE(DimInvoice,DimInvoice[InvoiceId],"_inv",DimInvoice[InvoiceDue],"_pay",[PaymentDue]),[_inv] - [_pay]
)
I get message Visual has exceed the available resource after many minutes.
Hi @glirme ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @glirme ,
If the [InvoiceDue] and [PaymentDue] can display correctly, we can try to use the following measure to meet your requirement:
Remainingamount = [InvoiceDue] - [PaymentDue]
Best regards,
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |