• Status: Solved
• Priority: Medium
• Security: Public
• Views: 466

# calculate total between start date and last day of current month - DAX (power pivot)

I have a traditional sales entries table with balances, due dates etc.

I want to calculate the total balance due between a given start start (e.g 1900) and the last day of the current month.

I know I could use the dates between function like below, however I would need to alter the range every month.

DATESBETWEEN(DateTime[DateKey],
DATE(2007,6,1),
DATE(2007,8,31)
))

How would I change the end date (bold) so that it always refers to the last day of the current month.

Mike
0
mikes6058
• 2
• 2
1 Solution

Commented:
Hi,

maybe

EOMONTH(TODAY(),0)

Regards
0

Author Commented:
Hasn't worked for me but I may not be using the corrent syntax

Could you rewrite the formula below.

=CALCULATE('CLE with remaining balances'[Sum of Rem. Amount],DATESBETWEEN('CLE with remaining balances'[Due Date],date(2000,01,01),DATE(2016,09,30)))
0

Commented:
then try

=CALCULATE('CLE with remaining balances'[Sum of Rem. Amount],DATESBETWEEN('CLE with remaining balances'[Due Date],date(2000,01,01),EOMONTH(TODAY(),0)))
1

Author Commented:
Brilliant!

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.