Calculating Historic Month End Balance of Transaction Data By Month

[Anonymous]

I feel like I am right near the finish line with this one but can't seem to get the correct and final answer. 

I have invoice transaction detail (I created some sample data that that I have included below) and I am trying to calculate what the outstanding total was at the end of each month. I have another date table that has the end date (EOM Date) of each month where I am trying to populate this as a calculated column.

So far, this is what I am using:

EOM Open Balance = CALCULATE(

    SUM(TransTable[Amount Due]),

    FILTER(ALLSELECTED(TransTable),

    TransTable[Transaction Date] <= EOM_DateTable[EOM Date] &&

    TransTable[Settle Date] > EOM_DateTable[EOM Date]

    ))

What I believe I am missing is picking up the line items where the settle date is still open/blank. The goal is to get the snapshot of what the open balance was historically at the end of each month.

Sample Data Here

Sample Expected Results:

EOM 8/31: $956,071.00

EOM 9/30: $1,280,565.00

Thank you in advance for any and all help!

[v-yueyunzh-msft · Accepted solution]

Hi, @Anonymous 

Here are the steps you can refer to:

This is my "End of month" test data :

(1)You can create a measure like this:

Measure = var _end_of_month = SELECTEDVALUE('End of Month'[End of Month])
var _t =FILTER( 'Trans' , 'Trans'[Transaction Date] <= _end_of_month && OR('Trans'[Settle Date] > _end_of_month , 'Trans'[Settle Date]= BLANK()))
return
SUMX(_t, [Amount])

(2)Then we put the [end of month] and the measure on the visual and we will meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thank you!

[mangaus1111]

Hi @Anonymous ,

see my pbi file. Let me know if the link to OneDrive does not work.

https://1drv.ms/u/s!Aj45jbu0mDVJix-vbGLCnjoZXanr?e=hGubfu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi @Anonymous 

Are you sure about your expected amounts!!??

Can you explain how did you get them from your excel file?

[Anonymous]

Hi, @aj1973 

I will certainly go back and add them up again. To get the expected amounts, I sorted by transaction date and summed all the amounts where:

• Transaction date <= Month End Date
• Settle Date > Month End Date (was still open at the time of month end)
• Settle Date = Blank

Thank you