1

I need to meet the following requirement and I am not sure what is the best way to do it.

At work, we have a table with transactions that may or may not have been paid. The date the transaction was made is indicated in the field created_date and the date the payment was made is indicated in the field completed_date (and it remains null if the payment was not made).

I am being asked for building a report in Power Bi and also a filter that allows indicating for the close of each month the amount of unpaid transactions.

I made a simple dummy example with only 3 transactions to better explain the requirement: Drive: https://drive.google.com/drive/folders/1IdWHQoiPgHx13HwRPV_8Nc4HvIRAGM_5?usp=sharing

enter image description here

If I analyze the closing of the periods 202302, 202303, and 202304, I should get the following: 202302: $10 because there was a transaction created in period 202302. 202303: $110 because the transaction from 202302 of $10 is still unpaid and a $100 transaction was also created in period 202303. 202304: $100 because the $10 transaction was paid.

So I should get this: enter image description here

Does anyone know if there's a DAX script or any transformations recommended to achieve this?

Thanks in advance!

Ladislao Csulak
  • 41
  • 4

1 Answers1

0

If you haven't already, create a Calendar table, and link the date column to the created_date column.

VAR AsOfDate = LASTDATE( 'Calendar'[Date] ) RETURN
CALCULATE(
    SUM( table[Amount] ),
    FILTER( table, ISBLANK( table[completed_date] ) || table[completed_date] > AsOfDate  ),
    DATESBETWEEN( 'Calendar'[Date], BLANK(), AsOfDate )
)

This will return the sum of amounts where the completed date is still blank or the completed date occurred after the end of the period you have selected in the Calendar table.

Luke_0
  • 779
  • 5
  • 20