Forum Discussion
Data Model for week over week changes
Hi All,
What is the best way for data modeling if I want to breakdown opportunity WoW changes into several categories -
1) new oppty : not in last week but in this week
2) out : not in this week but in last week
3) in both week with value change
For example, here is my weekly snapshot data and i want to know whethere an opportunity is new oppty,
| number | weeknum |
| OPTY111 | 1 |
| OPTY111 | 1 |
| OPTY222 | 2 |
Option 1: create a calulated column in the same table. If this is doable, what kind of DAX i should use ( Lookupvalue and Selected value to define weeknum?)
| number | weeknum | New |
| OPTY111 | 1 | No |
| OPTY111 | 1 | No |
| OPTY222 | 2 | Yes |
Option 2: breakdown the data into 2 data set: week 1 and week 2. create different models for new, value change, out then append queries together?
Thanks for your advice!
Irene
You may refer to the following post.
6 Replies
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?
If You have week wise Date
https://www.dropbox.com/s/0ec5u2nhxuo22eb/weeklysellerRank.pbix?dl=0
If you have Date wise Data
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
If you only want this week vs last week. FIlter week using slicer
