Forum Discussion

malcolms's avatar
malcolms
Frequent Visitor
9 years ago
Solved

Sum Dollar value between 2 dates

Hi,   I wanted to sum the dollar values between 2 dates. The print shot of my data model is attached. I could achieve part of this by implementing the method used in the below post. the problem is ...
Need Help
AlbertoFerrari's avatar
AlbertoFerrari
Most Valuable Professional
8 years ago

Hi Malcolm,

 

It looks like you are using a calculated column where you could easily use a measure, it is a very common mistake, if you are coming from an Excel background, where everything is a column :)

 

If your dataset is a small one (few millions rows), then you can author a measure like this one:

 

Dollars = 
VAR CurrentStartDate = MIN ( 'Date'[Date] )
VAR CurrentEndDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE ( 
	SUM ( Test[Dollar] ),
	Test[Start Date] <= CurrentStartDate,
	Test[End Date] >= CurrentEndDate
)

 

Being a measure, it is completely dynamic and will obey any filter you place in the visuals. If, on the other hand, you need to work on larger models, then I suggest you to take a look at this article I have written some time ago: https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/.

 

Moreover, I wrote a ton of these common calculations in the time intelligence section of this book, which I guess you might find interesting: https://www.sqlbi.com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/.

 

Please let me know if this solves your scenario.


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

malcolms's avatar
malcolms
Frequent Visitor
8 years ago

Hi Alberto,

 

Thanks for the reply. Sorry for the delay. I had issues posting my reply.

 

I did use a measure initially and had visited your site for reference.  The problem here is that the numbers do not roll up. The second Image shows the daily cost of the employee. So from 3rd May to 23rd May, I was paying $330. From 24th May to 31st May I was paying 763. In total I paid 330*21 & 763*8= 6930 +6104 = 13034. Instead, if you look at the 1st image you would see it displays $330 for May. I'm ok even if the values cumulates on a daily basis. But would like to see the value 9734 for the month of May.

 

Regards

Malcolm