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

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
Asked:
mikes6058
  • 2
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

maybe

EOMONTH(TODAY(),0)

Regards
0
 
mikes6058Author 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
 
Rgonzo1971Commented:
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
 
mikes6058Author 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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now