Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Is there anything wrong with this formula? I want it to give me the first day of the year in the current filter context but instead it gives me the first date in my dates table:
CALCULATE (MIN(tbl_Dates[FullDate]),
FILTER(ALL(tbl_Dates),
tbl_Dates[CalendarYear] = YEAR([DayInFilter_Last])
)
)
DayInFilterLast is a measure that returns the last date in the current filte: CALCULATE(MAX(tbl_Dates[FullDate])).
I have used this successfully as a starting point for calcualting MTD and YTD figures for this year and last year.
Determining the biginning and the end of the week last year is a bit more complicated and what I was doing wasn't working. The formula above is part of my attempt to work out why.
Thanks
Ian
Solved! Go to Solution.
To answer your question as to why you are getting the first date from your dates table is a bit involved. Someone else did the same thing that I commented on recently and it is a common mistake. Sorry but I can't remember what posting it was or i'd link to it for a full explanation. The brief answer is this:
"FILTER(ALL(tbl_Dates),tbl_Dates[CalendarYear] = YEAR([DayInFilter_Last])"
FILTER is an iterator that creates a row context. [DayInFilter_Last] is a measure that transitions the current row into the filter context making it so the result is always true for every date in your dates calendar. So MIN then returns the first date in the Dates table. Do this instead and it should work:
CALCULATE ( MIN ( tbl_Dates[FullDate] ), FILTER ( ALL ( tbl_Dates ), tbl_Dates[CalendarYear] = YEAR ( MAX ( tbl_Dates[FullDate] ) ) ) )
Hi,
Still struggling with this. I have tried to rebuild the model from scratch. I'm actually doing this one in Power Pivot, I hope that doesn't make any difference. I've reached the same point and I'm still getting stuck. Neither of the formulas below work. They both cause the same error: '... A single value for column 'FullDate' in table 'tbl_Dates' cannot be determined ...'.
I thought I had grasped the row context/filter context issue outlined in matbrice's reply but clearly I haven't.
As before, ThisYear is a measure that takes the MAX value from a slicer based on a disconnected table. I get the same problem when I replace MAX(ThisYear) with a literal value.
YTD_End__TY:=CALCULATE(
MAX(tbl_Dates[FullDate],
FILTER(ALL(tbl_Dates),
tbl_Dates[CalendarYearNum] = MAX([ThisYear])
)
))
YTD_End__TY:=CALCULATE(
MAX(tbl_Dates[FullDate],
FILTER(ALL(tbl_Dates),
tbl_Dates[CalendarYearNum] = 2017
)
))
That's done it! Thank you mattbrice.
How do I flag your post as the answer? Am I missing something obvious like I did with brackets in my formulas?
For the record, these now work:
CALCULATE(
MAX(tbl_Dates[FullDate]),
FILTER(ALL(tbl_Dates),
tbl_Dates[WeekNumber] = [ThisWeek]
)
)
CALCULATE(
MAX(tbl_Dates[FullDate]),
FILTER(ALL(tbl_Dates),
tbl_Dates[WeekNumber] = [ThisWeek]
&&
tbl_Dates[CalendarYearNum] = [ThisYear]
)
)
Hi matbrice,
Please don't read my lack of response as a loss of interest. I *think* this is the correct solution but have been unable to confirm. I had a five minute fiddle which was unsuccessful but hoping to test properly this weekend.
Thanks
Hi @IanR,
Your issue has been resolved? If it has been, please mark the corresponding reply as answer. More people will find workaround clearly.
Thanks,
Angelia
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |