Forum Discussion
Quite frustrated - need help
// Of course the one with hard-coded values works. // I have already explained it in my first post. // If you read it carefully, you'd know what to do... [LastWeekItems] = var __lastWeek = [LastWk] return Calculate ( [TotalItems], WeekofYr = __lastWeek, Year = 2020 // this should not be hard-coded )
You are not showing us everything, AviBI. What do you mean by "I defined a couple of variables in the Period Lookup Table"? This makes little sense. You can create variables in a formula, not in a table. Secondly, you can use variables in your measure formula instead of the hard-coded values in exactly the same places but the variables MUST be defined in the measure itself, not outside. Remember that the syntax you're using is syntax sugar since all conditions under CALCULATE must be tables. So, this:
calculate(
[measure],
T[Col] = a
)is always translated into:
calculate(
[measure],
filter(
all( T[Col] ),
T[Col] = a
)
)"a" must be a value, can't be a measure, for instance.
// Of course the one with hard-coded values works.
// I have already explained it in my first post.
// If you read it carefully, you'd know what to do...
[LastWeekItems] =
var __lastWeek = [LastWk]
return
Calculate (
[TotalItems],
WeekofYr = __lastWeek,
Year = 2020 // this should not be hard-coded
)
I did read your last post and did not quite understand as I am only used to defining straight forward measures (don't even know how to use "var" etc.)
Also understand that 2020 needs to be treated in a similar way as the week numbers. so I'll need to follow the same approach for the year as well
Thanks a lot for your help. I will now try this
