Forum Discussion
Calculate sum between date range
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
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!