Forum Discussion
Data Model for week over week changes
You may refer to the following post.
Why is OPTY1111 not new in your sample? I would think a column like the following would be what you are looking for:
Column =
VAR __EarliestWeek =
MINX(
FILTER(
ALL('Table'),
[number]=EARLIER([number])
),
[weeknum]
)
RETURN
IF([weeknum] = __EarliestWeek,"New","Old")
Thank you it works! Will check your book.😀
OPTY1111 is in innitial snapshot so not count as new. I have difficult to understand the logic of using MIN and Earlier..can you explain it to me?
Also, if I want to create the opposite view, which is oppty no longer exsist in this week - how should I write the column?Thanks!
OK, so the logic here goes like this. First EARLIER is a horrendously named function. The name makes sense if you understand the internal workings of DAX but for new people it is a nonsensical name that makes no sense. Think of EARLIER as "current row value". So, we ALL to break out of row context and analyze the entire table. We use FILTER to filter all records where the number column equals the current row's value (EARLIER) for the number column. We then use MINX to grab the lowest weeknum from that list. In theory we now have the first week that this thing appeared. So, we compare that number with the current row's weeknum column and now we know if the row we are on is the first one or not.
I'll have to think about that other one. So you want weeks that don't include an opportunity to display what?
Thanks a lot. Got you now. Indeed EARLIER naming is really confusing.
For opportunity that doesn't appear in current week it will be considered "out"
