Reconciliation Report with Dates issues

[Anildp]

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

[v-xicai · Accepted 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.

[Anildp]

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

---