Forum Discussion
Help needed: Sum values based on date checking, two related tables.
I have two tables, joined on location. I need to determine what the daily budget hours from the LocationBudget table based on the location and date in the LocationCalendar table.
What I'm trying to accomplish:
IF LC.Date greater than equal LB.BudgetStart
AND LC.Date less than equal LB.BudgetEnd
THEN SUM(LB.BudgetHours ) as LC.DailyHours
Table: LocationCalendar (LC)
| Location | Date | DailyHours |
| House1 | 7/1/20 | 8 |
| House1 | 7/15/20 | 8 |
| House1 | 8/15/20 | 4 |
| House2 | 7/1/20 | 16 |
| House2 | 7/15/20 | 16 |
| House2 | 8/15/20 | 10 |
Table: LocationBudget (LB)
| Location | BudgetStart | BudgetEnd | BudgetHours |
| House1 | 7/1/20 | 7/31/20 | 8 |
| House1 | 8/1/20 | 8/31/20 | 4 |
| House2 | 7/1/20 | 7/31/20 | 16 |
| House2 | 8/1/20 | 8/31/20 | 10 |
Any help would be greatly appreciated. Thanks so much!
Here is a column expression that should get your desired result.
Daily Hours =
VAR thisdate = LocationCalendar[Date]
RETURN
CALCULATE (
MIN ( LocationBudget[BudgetHours] ),
LocationBudget[BudgetStart] <= thisdate,
LocationBudget[BudgetEnd] >= thisdate
)Regards,
Pat
2 Replies
- mahoneypat
Microsoft Employee
Here is a column expression that should get your desired result.
Daily Hours =
VAR thisdate = LocationCalendar[Date]
RETURN
CALCULATE (
MIN ( LocationBudget[BudgetHours] ),
LocationBudget[BudgetStart] <= thisdate,
LocationBudget[BudgetEnd] >= thisdate
)Regards,
Pat
vpanchuHelper I
Anonymous Sorry i am kind of finding it difficult to understand , Can you show me how should your output table look like so that i can try the logic if possible