Reconciliation Report with Dates issues

Question

Hi All,&nbsp;I have a unique issue with me where I need to reconcile data between two system's&nbsp;Below listed is the sample query,&nbsp;How do I achieve this?&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;&nbsp; FACT.RLATD_PYMT_CID&nbsp; AS PAYMENT_ID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp; FACT.PYMT_RCNCLN_TYPE_CODE,&nbsp;&nbsp;&nbsp;&nbsp; FACT.PYMT_TYPE_NAME,&nbsp;&nbsp;&nbsp;&nbsp; FACT.CAST(PYMT_STLMT_DTTM AS DATE) AS DEPOSIT_DATE,&nbsp;&nbsp;&nbsp;&nbsp; FACT. CAST(BILLG_PYMT_DTTM AS DATE) AS BILLING_APPLIED_DATE,&nbsp;&nbsp;&nbsp;&nbsp; FACT.BILLG_ACCT_NBR AS BAN,&nbsp;&nbsp;&nbsp;&nbsp; FACT.COALESCE(BILLG_PYMT_AMT, PYMT_STLMT_AMT) AS AMOUNT&nbsp;FROM FIN_V.PYMT_RECON FACT&nbsp;where-- FOR PAYMENT's&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CAST(BILLG_PYMT_DTTM AS DATE) BETWEEN (1ST DAY OF PERIOD/CURRENT_MONTH) AND (LAST DAY OF PERIOD/CURRENT_MONTH)&nbsp;How should I achieve this ??&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;-- FOR REFUNDS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CAST(PYMT_STLMT_DTTM AS DATE) BETWEEN (1ST DAY OF PERIOD/CURRENT_MONTH) AND (LAST DAY OF PERIOD/CURRENT_MONTH)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;how to achieve this?&nbsp;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.&nbsp;the user will have Period on the report&nbsp; and the user will select the period on the top of the report, Forex: April 2019&nbsp;then the report should run both queries and show the data in a single table.&nbsp;please help me with this&nbsp;&nbsp;

v-xicai · Accepted Answer

Hi,&nbsp;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.&nbsp;Date_Table=CALENDARAUTO().&nbsp;Then create calculated table Table1 like DAX below.&nbsp;Table1=CALCULATETABLE(SELECTCOLUMNS(FIN_V.PYMT_RECON,"PAYMENT_ID",FIN_V.PYMT_RECON[RLATD_PYMT_CID],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "PYMT_RCNCLN_TYPE_CODE",FIN_V.PYMT_RECON[PYMT_RCNCLN_TYPE_CODE],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "PYMT_TYPE_NAME",FIN_V.PYMT_RECON[PYMT_TYPE_NAME],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "DEPOSIT_DATE",FIN_V.PYMT_RECON[CAST(PYMT_STLMT_DTTM AS DATE)],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "BILLING_APPLIED_DATE",FIN_V.PYMT_RECON[CAST(BILLG_PYMT_DTTM AS DATE)],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "BAN,",FIN_V.PYMT_RECON[BILLG_ACCT_NBR)],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "AMOUNT,",FIN_V.PYMT_RECON[COALESCE(BILLG_PYMT_AMT, PYMT_STLMT_AMT)])&nbsp;,FILTER(ALLSELECTED(Date_Table),Date_Table[Date]&gt;=(EOMONTH(Date_Table[Date],-1)+1)&amp;&amp;Date_Table[Date]&lt;=EOMONTH(Date_Table[Date],0)))&nbsp;Best Regards,Amy&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

Forum Discussion

Reconciliation Report with Dates issues

2 Replies

Related content

Usage Metric report Refresh Issue

Report access issues

reporting issues

Report Access permissions issue

Report server Issue