Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
glirme
Advocate I
Advocate I

how to measure in 2 tables and based on slicer value

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

10 REPLIES 10
amitchandak
Super User
Super User

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.