Overdue payment last 12 month

[CamFi]

Hi all,

i have tried to work out this problem for quite some time now but haven't been able to find a solution. 

i have a table  "debtor" with the following columns: [AmountLCY], [Due Date], [End Date], [Posting Date]. the debtor tables has a relationship to my calendar table. 

what i want to do is to calculate the amount overdue / the outstandings at the end of the month for the last 12 month.  i have tried several different methods, but none of them with great success. 

my best guess was to use

CALCULATE(
    SUM( debtor[amountLCY]),
    FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] = MAX( 'Calendar'[Date] )

but as it can bee seen above this expression are only taking into account the bills not yet paid, and not the bills that was overdue in earlier month but now is paid . 

    hope somebody can help me.

Many thanks

[CamFi · Accepted solution]

Thank you for the trying, i figured out my mistake was in my data and not in the DAX measure

i ended up afterwards just using 

CALCULATE(SUM( debtor[amountLCY]),
    FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] = MAX( 'Calendar'[Date] )

 and it worked 

[v-stephen-msft]

Hi @CamFi ,

I am a little confused.

Please explain the logic of the bill that was not overdue last month but has now been paid.

And what do the three columns [AmountLCY], [Due Date], [End Date] and [Posting Date] mean?

Best Regards,

Stephen Tao

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

[amitchandak]

@CamFi , You can try like

CALCULATE(
SUM( debtor[amountLCY]),
FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] = eomonth( 'Calendar'[Date],0) ))

Can you share sample data and sample output in table format?