Forum Discussion

kenneth0596's avatar
kenneth0596
Frequent Visitor
5 years ago
Solved

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[Sta...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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

  • kenneth0596's avatar
    kenneth0596
    Frequent Visitor
    5 years ago

    Thank you, Jihwan!