How to remove duplicates as per the date ranges in power query

Power Query
1

Hi Team,
I am just trying to formulate for monthly count of the customers which paid over the period.
I was able to formulate for the one with full payments. however if they paid partially over the period, our ERP produce the record of payment date and amount in different lines. in my Power query or excel calculation, I have duplicated records as attached.

Please guide me to get the power query formula corrected.
Solution_MOTH_RADCal_-1.xlsx (34.9 KB)
Many thanks,
regards,
Aye

2

Hi @ammu, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

3

Hi @ammu,

Try the measure below. Assumptions:

  1. A date table exists, with the following columns:
    Month Starting
    Month Ending

  2. The date table is not joined to the data table.

  3. The field in the matrix columns is Date[Month Ending].

     Customer Count = 
 VAR vCustID =
     MAX ( Payment[CustID] )
 VAR vMonthStarting =
     MAX ( 'Date'[Month Starting] )
 VAR vMonthEnding =
     MAX ( 'Date'[Month Ending] )
 VAR vDepartureDate =
     MAX ( Payment[DepartureDate] )
 VAR vReceivedDate =
     MAX ( Payment[ReceivedDate] )
 VAR vPmtType =
     MAX ( Payment[Full/Partial] )
 VAR vResult =
     SWITCH (
         vPmtType,
         "Partial",
             IF ( vReceivedDate >= vMonthStarting && vReceivedDate <= vMonthEnding, 1, 0 ),
         "Full",
             SWITCH (
                 TRUE (),
                 ISBLANK ( vDepartureDate ), IF ( vMonthEnding < vReceivedDate, 0, 1 ),
                 NOT ISBLANK ( vDepartureDate ),
                     IF ( vMonthEnding < vReceivedDate || vMonthEnding > vDepartureDate, 0, 1 )
             )
     )
 RETURN
     vResult

image
image1197Ă—568 98.9 KB

1 Like
4

Hi @ammu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!