Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Create bar graph of unpaid transactions having transaction created and payment date



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 a report 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:




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:



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

Thanks in advance!




Solution Sage
Solution Sage

Hello @Laocsulak,

There are many ways to tackle your problem. The one I propose below may not be optimal but it works.

The idea is to create an additional table and a measure.

The additional table is to be created with this [DAX] code:

NewTable = 
    SUMMARIZE ( data, [created_month], "amount", SUM (data[amount] ) ),
    SUMMARIZE ( FILTER ( data, NOT ISBLANK ( data[completed_month] ) ), 
                "amount", -SUM ( data[amount] ) ))

It will look like that:


The measure should look like that:


Measure = 
VAR CurrentMonth = MAX ( NewTable[created_month] )
RETURN CALCULATE ( SUM ( NewTable[amount] ), ALL ( NewTable ),NewTable[created_month] <= CurrentMonth )


Once done, you create a bar chart. 


Then you can rename axis and enjoy the result. 

Best Regards,


My YouTube vlog in English

My YouTube vlog in Russian

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors