Forum Discussion
Calculate sum between date range
Hi all,
Below is my sample data.
| Fee | Start Date | End Date |
| 12000 | 1/1/2021 | 6/30/2021 |
| 12000 | 1/1/2021 | 12/31/2021 |
I have also created a custom date table (CALENDAR(MIN(Sample[StartDate].[Date]),MAX(Sample[EndDate].[Date])). What I'm trying to achieve is a simple bar chart where x axis is the month from date table and y axis is the fee column from sample data. I know this can be done by extracting all the dates between start and end date and then calculate the daily cost (fee/# of days) however this method would generate too much data and not sustainable in long run.
Any help would be greatly appreciated! Thanks!
Hi, kenneth0596
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file, and all steps are numbered in front of each measure.
1 Fee Total =CALCULATE (SUMX( 'Sample', COUNTROWS(Dates) * 'Sample'[Fee] /(1+ DATEDIFF('Sample'[Start Date], 'Sample'[End Date],DAY ))),FILTER ('Sample','Sample'[Start Date] <= MAX ( Dates[Date] )&& 'Sample'[End Date] >= MIN ( Dates[Date] )))2 Fee Total Fix =SUMX(VALUES(Dates[Month Name]), [1 Fee Total])https://www.dropbox.com/s/sk5hf1pr4sku69i/kenneth.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
5 Replies
Hi, kenneth0596
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file, and all steps are numbered in front of each measure.
1 Fee Total =CALCULATE (SUMX( 'Sample', COUNTROWS(Dates) * 'Sample'[Fee] /(1+ DATEDIFF('Sample'[Start Date], 'Sample'[End Date],DAY ))),FILTER ('Sample','Sample'[Start Date] <= MAX ( Dates[Date] )&& 'Sample'[End Date] >= MIN ( Dates[Date] )))2 Fee Total Fix =SUMX(VALUES(Dates[Month Name]), [1 Fee Total])https://www.dropbox.com/s/sk5hf1pr4sku69i/kenneth.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Thank you, Jihwan!
Hi Jihwan_Kim,
I have a follow up question hoping you can help me.
Basicially I now have to merge this table (Table 1) with other ones (Table 2, Table 3) and create a matrix out of the merged data.
I also have to establish a relationship between merged table and date table in order for the data from Table 2 + Table 3 to populate correctly.
The relationship however will cause an issue with the measure, it only returns the daily rate instead of total (daily rate * # of days). Is there any way to fix this?
Thank you in advance for your time!
Hi, kenneth0596
I am not sure whether I understood your question correctly.
If you want to create an active relationship, I suggest re-create my previous measure.
If you want to create an inactive relationship, I suggest using the USERELATIONSHIP DAX function for other measures.
Jihwan, thanks for replying!
I have appended three tables (table 1, table 2, table 3) into one and then use the data from appended table to populate a matrix.
What I have noticed is that data from table 1 (the one that uses your measures) only works if I disable the relationship between appended table and date table. The data from table 2 and table 3, however, needs an active relationship. So I'm now in this dilemma where part of the data requires relationship and other part doesnt.
Once again, thank you for your advice!
