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 have a unique issue with me where I need to reconcile data between two system's
Below listed is the sample query,
How do I achieve this?
SELECT
FACT.RLATD_PYMT_CID AS PAYMENT_ID,
FACT.PYMT_RCNCLN_TYPE_CODE,
FACT.PYMT_TYPE_NAME,
FACT.CAST(PYMT_STLMT_DTTM AS DATE) AS DEPOSIT_DATE,
FACT. CAST(BILLG_PYMT_DTTM AS DATE) AS BILLING_APPLIED_DATE,
FACT.BILLG_ACCT_NBR AS BAN,
FACT.COALESCE(BILLG_PYMT_AMT, PYMT_STLMT_AMT) AS AMOUNT
FROM FIN_V.PYMT_RECON FACT
where
-- FOR PAYMENT's
CAST(BILLG_PYMT_DTTM AS DATE) BETWEEN (1ST DAY OF PERIOD/CURRENT_MONTH) AND (LAST DAY OF PERIOD/CURRENT_MONTH)
How should I achieve this ??
-- FOR REFUNDS
CAST(PYMT_STLMT_DTTM AS DATE) BETWEEN (1ST DAY OF PERIOD/CURRENT_MONTH) AND (LAST DAY OF PERIOD/CURRENT_MONTH)
how to achieve this?
These are 2 queries, one on the payment side and another on the Refund's side and the user is expecting to display them on the same table.
the user will have Period on the report and the user will select the period on the top of the report, Forex: April 2019
then the report should run both queries and show the data in a single table.
please help me with this
Solved! Go to Solution.
Hi,
You can create calculated table Date_Table, put the Date_Table[Date] on slicer, and then create relationships between Date_Table with table PAYMENT and table Refund on Date field.
Date_Table=CALENDARAUTO().
Then create calculated table Table1 like DAX below.
Table1=CALCULATETABLE(SELECTCOLUMNS(FIN_V.PYMT_RECON,"PAYMENT_ID",FIN_V.PYMT_RECON[RLATD_PYMT_CID],
"PYMT_RCNCLN_TYPE_CODE",FIN_V.PYMT_RECON[PYMT_RCNCLN_TYPE_CODE],
"PYMT_TYPE_NAME",FIN_V.PYMT_RECON[PYMT_TYPE_NAME],
"DEPOSIT_DATE",FIN_V.PYMT_RECON[CAST(PYMT_STLMT_DTTM AS DATE)],
"BILLING_APPLIED_DATE",FIN_V.PYMT_RECON[CAST(BILLG_PYMT_DTTM AS DATE)],
"BAN,",FIN_V.PYMT_RECON[BILLG_ACCT_NBR)],
"AMOUNT,",FIN_V.PYMT_RECON[COALESCE(BILLG_PYMT_AMT, PYMT_STLMT_AMT)])
,FILTER(ALLSELECTED(Date_Table),Date_Table[Date]>=(EOMONTH(Date_Table[Date],-1)+1)&&Date_Table[Date]<=EOMONTH(Date_Table[Date],0)))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You can create calculated table Date_Table, put the Date_Table[Date] on slicer, and then create relationships between Date_Table with table PAYMENT and table Refund on Date field.
Date_Table=CALENDARAUTO().
Then create calculated table Table1 like DAX below.
Table1=CALCULATETABLE(SELECTCOLUMNS(FIN_V.PYMT_RECON,"PAYMENT_ID",FIN_V.PYMT_RECON[RLATD_PYMT_CID],
"PYMT_RCNCLN_TYPE_CODE",FIN_V.PYMT_RECON[PYMT_RCNCLN_TYPE_CODE],
"PYMT_TYPE_NAME",FIN_V.PYMT_RECON[PYMT_TYPE_NAME],
"DEPOSIT_DATE",FIN_V.PYMT_RECON[CAST(PYMT_STLMT_DTTM AS DATE)],
"BILLING_APPLIED_DATE",FIN_V.PYMT_RECON[CAST(BILLG_PYMT_DTTM AS DATE)],
"BAN,",FIN_V.PYMT_RECON[BILLG_ACCT_NBR)],
"AMOUNT,",FIN_V.PYMT_RECON[COALESCE(BILLG_PYMT_AMT, PYMT_STLMT_AMT)])
,FILTER(ALLSELECTED(Date_Table),Date_Table[Date]>=(EOMONTH(Date_Table[Date],-1)+1)&&Date_Table[Date]<=EOMONTH(Date_Table[Date],0)))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |