End of Month in Power Query

[abriggs]

I need help with M code to do this in Power Query. The customer account balance table creates an entry every day whether the customers balance has changed or not (resulting in multiple duplicate entries). This is helpful when viewing the aging trend day by day....but not helpful when I need to view it by month. Therefore, I want to insert a column that will insert the total balance due if the balance date is the end of the month else insert 0. I'm not very familiar with M Code so I'm unsure how to do this. the code that I've written is this, but it's just returning with all 0's. 

if [BalanceDate]=Date.EndOfMonth then [TotalBalanceDue] else 0 

Assuming I'm not using the EndOfMonth properly? 

Please help or if there is a better solution to get around this I'm open to that as well. 

Thank you so much!  

[AlB · Accepted solution]

Hi @abriggs 

Try

if [BalanceDate]=Date.EndOfMonth([BalanceDate]) then [TotalBalanceDue] else 0 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[abriggs]

Thank you - this worked! 

Looking to take it one step further and add...if it's not end of the month in the current month, then total for that month as of today. So example, since we haven't hit end of month for October, I'd like it to pull through the balance for today instead of 0. 

Thoughts? Thank you 🙂