cancel
Showing results for
Did you mean:
Helper I

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

Hello,

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?

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 =
UNION (
SUMMARIZE ( data, [created_month], "amount", SUM (data[amount] ) ),
SUMMARIZE ( FILTER ( data, NOT ISBLANK ( data[completed_month] ) ),
[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,

Alexander

Announcements

#### Join Arun Ulag at MPPC23

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

#### Power BI September 2023 Update

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

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

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