cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anildp Regular Visitor
Regular Visitor

Reconciliation Report with Dates issues

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xicai Super Contributor
Super Contributor

Re: Reconciliation Report with Dates issues

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.

 

2 REPLIES 2
v-xicai Super Contributor
Super Contributor

Re: Reconciliation Report with Dates issues

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.

 

Anildp Regular Visitor
Regular Visitor

Re: Reconciliation Report with Dates issues

thanks @v-xicai  I got the solution in another format as well.