comparing two dates from unrelated tables in Power BI

[Mamerion]

Hi there,

I'd be thankful if you could help me with a DAX to compare two dates from diferent tables. the tables are not related and due to performance issues I don't want to merge them and then create a custom column to flag out the anomalies. 

 we need to list the invoices that their payment dates are prior to their issuing date. as you may find from the model, the invoice amount and date are in link_invoice table whereas the payment date is in the voucherHeader table which are related through the voucherLine table.

here you are with the SQL query which I need to convert it to DAX:

select LI.VoucherCode, LI.DateKey, vh.PaymentDateKey,*
from [FIN.AP].[Link_Invoice] LI
inner join [FIN.AP].Fact_VoucherLine VL on li.VoucherLineItemKey = vl.LineID
inner join [FIN.AP].Fact_VoucherHeader VH on vh.HeaderID = vl.HeaderKey
where PaymentDateKey < li.DateKey

Thanks in advance for your help.

[v-yuezhe-msft]

@Mamerion,

Create a new table using DAX below.

Newtable = FILTER(CROSSJOIN(FILTER(CROSSJOIN(Link_Invoice,Fact_VoucherLine),Link_Invoice[VouncherLineItemKey]=Fact_VoucherLine[LineID]),SELECTCOLUMNS(Fact_VouncherHeader,"HeaderID",Fact_VouncherHeader[HeaderID],"paymentdatekey",Fact_VouncherHeader[PaymentDateKey])),Fact_VoucherLine[HeaderKey]=[HeaderID])

Then create a new column in the new table, drag the column to visual level filter and set its value to 1.

checkcolumn = IF(Newtable[paymentdatekey]<Newtable[DateKey],1,0)

Regards,
Lydia

[Mamerion]

Thanks Lydia,

Appreciate your help,  what you are suggesting is  to create a new table before I can flag the records out, and that is what I try to avoid due to the performance issues. 

I was hoping to find a way to select different fields from the tables that are not directly connected  and use them in a calculation (without the need to merge the tables)

Cheers,

M

[v-yuezhe-msft]

@Mamerion,

What other calculations would you like to use? You can create a table variable using above DAX in a measure or column.

Regards,
Lydia

[Mamerion]

Thanks Lydia,

A CalculateTable or Merging the tables to compare the dates will definitely work but I was trying to avoid doing so. BTW, I've decided to create a view in the backend and connect to the SQL View to visualise the query results.

However; for others reference's,  the above first DAX (CalculateTable) throws a reference error when I try to run it!

I have another scenario where I need to find the duplicate vendor names in Dim_Vendor who's got different vendor code in the other two tables. this is to find if the same Vendor has got more than one address or more than one banking details due to a human error like entering a wrong vendorCode. Here, i can use RelatedTable because they are directly related.

with CTE
as
(
select Count(*)as Count,Name from [FIN.AP].Dim_Vendor V
inner join [FIN.AP].Dim_VendorAddress Va on V.VendorID = Va.VendorKey
inner join [FIN.AP].Dim_VendorBankdetail Vb on V.VendorID = Vb.VendorKey
where V.Active =1 
Group by [Name]
Having count(*) > 1
)
Select * from [FIN.AP].Dim_Vendor 
Where Name in 
(select distinct name from cTe) 
order by [Name] 

 I'm trying to write a DAX  to find same names with different codes(as above SQL query).

worth mentioning that we can simply count the rows in DIM_Vendor, create a bar chart and select the name as Y axis and CountRow as the x axis. here, we've grouped the vendors by name. then we can add a filter to find the names that have more than one rows(duplicates). the drawback is that when there is no duplicate then the chart would be showing nothing or we'll have an empty table in our visualisation.  That's why I'd like to come up with measure that groups by names to show the number of duplicates occurring.

thanks again 

[opticshrew]

Hi @Mamerion

I don't have an answer yet for you but I just have a couple of probing questions:

• If this is to do with invoices and payments is their a ledger table that you could work from?
• What are you trying to achieve exactly. Just a view where invoices were payed before they were issued?

Thanks,

J

[Mamerion]

Thanks for your reply J,

we have a couple of static repots for auditing financial transactions. we try to make them interactive by replicating them into the PowerBI environment. one of these auditings is to find the peyments that are done prior to having an invoice.

we do have ledgers against invoices, purchase orders, and vouchers. but again, invoice table has got the invoice date, and voucher table has got the payment date. I wonder how use of ledger could help to answer this question. 

We already provided insights and visualized other important factors within finance space; so, it is rather costly for us to change the star schema in the backend as it will affect all other measures. I can add the PaymentDateKey as a new column to the invoice table and resolve my issue; but, I am really keen to find if there is any way that I can manage such senarios in DAX.

It would be very handy and useful as we don't wanna keep touching our backend model whenever we need to meet a new requirement. i was thinking if we could have a nested RELATEDTABLE() then we could manage snowflakes and write measures between FACT tables and their outer Dimensions?

Regards,

M