Forum Discussion
Lookup value if date is between two dates
I don't think you've told us all about the model... I suspect there are relationships between the two tables based on the date fields.
Try this
CreatedInSprint = var __date = WorkItems[fields_SystemCreatedDate] return MAXX( FILTER( Sprints; AND( Sprints[attributes_startDate] <= __date, __date <= Sprints[attributes_finishDate] ) ), Sprints[SprintNo] )
This should work correctly on the assumption that there is always at most one sprint returned by the logical condition in FILTER. If there happen to be many, then the maximum SprintNo will be returned.
Best
Darek
I’m trying to create a formula to show the QBEstimate.Monthlyfee for the righg billing period
My qBEstimate Table has a QBEstimate.EsStartDate, QBEstimate.EsEndDate and monthly fee. I’m trying to create a matrix to show the fee by TDate.Billing Month
I know the problem is in my relationships but I can’t set the set QBEstimate.EsStartDate and QBEstimate.EsEndDate to the TDate.Billing Month
This is my measure – it returns te right values for some months but not all.
DAX measure
MSSMonthlyFees =
CALCULATE(
SUM(QBEstimate[MonthlyFee]),
FILTER(QBEstimate,
QBEstimate[EsStartDate] <= min(TDate[Billing Month]) &&
QBEstimate[EsEndDate] >= max(TDate[Billing Month])
)
)
All help welcome
Thank you
TDATE Table
TDate = ADDCOLUMNS(
CALENDAR(date(2021,1,1), date(2022,12,31)),
"Month", FORMAT([Date],"mmm YY"),
"MonthOrder", MONTH([Date]),
"Year",YEAR([Date]),
"Week", WEEKNUM([Date]),
"WeekYear", concatenate(YEAR([Date]),WEEKNUM([Date])),
"Billing Month",
VAR DayNumber = WEEKDAY ( [Date], 1 ) RETURN IF(DayNumber = 7,[Date] - 1, [Date] + 6 - DayNumber)
)
QBEstimate Table
Id | CustomerRef_Value | EsStartDate | ESEndDate | MonthlyFee |
17563 | 1252 | 4/21/2022 | 10/22/2022 | $9,900.00 |
17558 | 1247 | 4/1/2022 | 4/1/2023 | $21,991.67 |
17494 | 1185 | 2/13/2022 | 2/13/2023 | $19,227.67 |
17531 | 1216 | 8/21/2021 | 8/19/2022 | $25,695.00 |
17530 | 1215 | 8/19/2021 | 8/19/2022 | $10,075.00 |
17492 | 1183 | 7/30/2021 | 10/22/2022 | $4,070.30 |
17518 | 1204 | 7/1/2021 | 5/1/2022 | $20,720.74 |
17487 | 1159 | 6/30/2021 | 6/30/2022 | $35,000.00 |
17523 | 1165 | 8/22/2020 | 10/22/2022 | $15,578.81 |