Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
irenelitw629
Helper II
Helper II

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,

 

numberweeknum
OPTY1111
OPTY1111
OPTY2222

 

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?)

numberweeknumNew 
OPTY1111No
OPTY1111No
OPTY2222Yes

 

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

1 ACCEPTED SOLUTION

@irenelitw629 

 

You may refer to the following post.

http://community.powerbi.com/t5/Desktop/Comparing-a-month-to-it-s-relative-previous-month-in-a-table...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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")

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

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?

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

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"

@irenelitw629 

 

You may refer to the following post.

http://community.powerbi.com/t5/Desktop/Comparing-a-month-to-it-s-relative-previous-month-in-a-table...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.