Hi,
I’m working on a project and resource planner app and I need to calculate all future projects based on the technology they’re using to know how many resources we’ll have by the time of the project. I have a table Opportunities which contains all possible future projects, I’ve already made a slice that takes out only the relevant projects as well as an expression that calculates how many months it will span over and divide the mandays to distribute across those months.
I also have another table called VendorDates and this contains all the months for the coming year, I’ve automated this so a new row gets added for the next year at the first of every month.
Now for the expression I’m having trouble with: In the Opportunity Amount column I need to calculate how many days are required for future projects. How could I add up all those days from the Opportunities table that happen in during a month that also have the same Vendor in VendorDates table.
TLDR: I need to add up all the Mandays from Opportunities that are happening during that month in the VendorDates table that also have the same Vendor for example “Magnolia”.
If an Opportunity spans from 1/1/2022 until 3/1/2022 and has 30 Mandays, 10 will be distributed over those 3 months (The expression to get the monthly total of 10 I have already)
Thanks in advance for any help!
1 Like
Oh man, I kept reading “Mandays” as “Mondays” and I was thinking that it must really suck having 30 mondays in 3 months.
Try this:
SUM(
SELECT(
Opportunities[Mandays],
AND(
[Vendor]=[_THISROW].[Vendor],
OR(
MONTH([Start Date])=MONTH([_THISROW].[MonthDate],
MONTH([End Date])=MONTH([_THISROW].[MonthDate],
)
)
)
)
I didn’t know whether to take your start or end date for the expression, so I took both, does this work for you?
2 Likes
Haha that would indeed be horrible. I don’t know if the word mandays really gets used in English so my bad. Thanks for your answer though! But I don’t seem to get it to work.
I changed it a bit to fit my project since I changed it for the post here so it might be more clear.
After changing and trying to fix the parentheses I still get an error.
The Close Date will essentially be the Start Date of the project so if a month, for example May would be (between) the Start and or End Date then it should be added up for that month.
I’ll think of a better way to explain this and will reply again later. Thanks again for looking into it 
Sorry for that, the expression was lacking a couple parentheses, let me try again taking into account what you said:
SUM(
SELECT(
Opportunities[Mandagen],
AND(
[Vendor]=[_THISROW].[Vendor],
[Start Date]<=[_THISROW].[MonthDate],
[End Date]>[_THISROW].[MonthDate],
)
)
)
1 Like
Thanks for the response, I do however still get an error I don’t fully understand since I think it contains elements. The expression you made does look like it’s supposed to do exactly what I need.
Can you press the “Test” button and see if it returns anything?
I cannot, I think only expressions that don’t detect an error can be ran as a test.
I HAVE FOUND MY ERROR !
SUM(
SELECT(
Opportunities[Mandagen],
AND(
[Vendor]=[_THISROW].[Vendor],
[Start Date]<=[_THISROW].[MonthDate],
[End Date]>[_THISROW].[MonthDate]
)
)
)
3 Likes
That fixed it! Now I’m getting a different error though, I tried removing the line giving the error but then it gave me one on another line. I really don’t know what the problem is here, I feel bad to keep bothering you haha.
Getting a different error is called progress !
LIST? is one of your “Vendor” columns an enum list? shouldn’t it just be a ref, enum/ref?
You can remove the “Opportunities” from within the AND(), my expression should work
2 Likes
Yes, Vendors is indeed an enumlist. Opportunities could have one or more vendors so I thought using an enumlist is the way to achieve this is it not?
I changed it to a ref and the expression doesn’t give any errors anymore and works! Thanks again for all the help you provided!
Yeah that’s the way, I just didn’t know it was an enum list beforehand hehe, that changes the expression a bit, so only the [Vendor] column in opportunities is an enum list? and the other one isn’t? if that’s the case try this:
SUM(
SELECT(
Opportunities[Mandagen],
AND(
ISNOTBLANK(INTERCEPT([Vendor],LIST([_THISROW].[Vendor]))),
[Start Date]<=[_THISROW].[MonthDate],
[End Date]>[_THISROW].[MonthDate]
)
)
)
1 Like
Alright, my bad I forgot to point that out haha. It works this way for the enum list, I just changed INTERCEPT to INTERSECT since it didn’t recognize that and I think it might’ve been a typo.
Thank you very much for all the help and time you spent to fix this
2 Likes
