Replicating records and imputation with date differences in power bi

Question

having a dataset with specific columns - CustomerCode, Customer, Transactionnumber, Transaction_date, Due_date, and payment_date. I'm trying to create a summarized table with a new column called date that follows certain rules based on conditions from other columns.

Here's what I need to achieve:

• If pay_date is not null, I want to add Due_Date to a new column called date.

• If pay_date is null, I need to calculate the months between Due_Date and Transaction_Date and add the last day of each month in the date column.

• Additionally, I want to duplicate the entire record for each month.

For instance, consider this example:

Original table:

Transaction_Date	Due_Date	Payment_Date
1-Jan-2023	1-May-2023

Then the desired output table should be as below

Month	Transaction_date	Due_Date
31-Jan-2023	1-Jan-2023	1-May-2023
28-Feb-2023	1-Jan-2023	1-May-2023
31-Mar-2023	1-Jan-2023	1-May-2023
30-Apr-2023	1-Jan-2023	1-May-2023
31-May-2023	1-Jan-2023	1-May-2023

I need help with the DAX code or steps to achieve this summarization in Power BI. How can I create a calculated table that follows these rules based on the conditions in the pay_date column? Any insights or guidance on the DAX functions or steps to perform this summarization would be greatly appreciated.

Answer 1

Table1:

Create a new date table as follows:

Date = CALENDARAUTO() 

Create a new table as follows:

Table2 = 
GENERATE(
    Table1, 
    FILTER(
        DATESBETWEEN('Date'[Date], Table1[Transaction_Date], EOMONTH( Table1[Due_Date],0)),
        'Date'[Date] = EOMONTH('Date'[Date],0)
    )
)

---

Supplimental

Adding on to this answer:

• With the conditional check on "pay_date".
• Doesn't need a Date table created, however if you have one already in your model, then tweak to use the same concept as above.

TableTR 2 = 
  var withPDate = 
    SELECTCOLUMNS(
      FILTER(TableTR, NOT ISBLANK(TableTR[Payment_Date])),
      "Month", [Due_Date],
      "Transaction_Date", [Transaction_Date],
      "Due_Date", [Due_Date]
    )
  
  var withoutPDate = 
    SELECTCOLUMNS(
      GENERATE(
        FILTER(TableTR, ISBLANK(TableTR[Payment_Date])),
        FILTER(
          CALENDAR(TableTR[Transaction_Date], EOMONTH( TableTR[Due_Date], 0) ),
          [Date] = EOMONTH([Date], 0)
        )
      ),
      "Month", [Date],
      "Transaction_Date", [Transaction_Date],
      "Due_Date", [Due_Date]
    )

  return UNION(withPDate, withoutPDate)