cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Two34
Frequent Visitor

If date is between 2 dates that sum

Hello,

 

I have two tables one that has start date, end date , average value per day and the other that has Calendar.

I want to create a column in the calendar table with the total averages per day.

For example in case of 02-Feb i need to look in all the rows where 02-Feb is in between [Start Date-End Date) and sum the Avg per Day.

I tried the formula in the print screen but I got an error.

Many Thanks

Issue.png

 

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

try this (not tested but it should work)

 

=
CALCULATE (
    SUM ( 'Table1'[Avg per Date] ),
    'Table1'[Start date] <= EARLIER ( 'Calendar'[Date] ),
    'Table1'[End Date] >= EARLIER ( 'Calendar'[Date] )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

try this (not tested but it should work)

 

=
CALCULATE (
    SUM ( 'Table1'[Avg per Date] ),
    'Table1'[Start date] <= EARLIER ( 'Calendar'[Date] ),
    'Table1'[End Date] >= EARLIER ( 'Calendar'[Date] )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Two34
Frequent Visitor

Thanks for the help!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors