Search code examples
powerbidaxpowerbi-desktopcalculated-columns

Replicating records and imputation with date differences in power bi

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.

Solution

  • Table1:

    enter image description here

    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)
    )
)

    enter image description here

    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)