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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors