cancel
Showing results for
Did you mean:
Frequent Visitor

## Week over Week change for the same document

Hello guys!

I have a table called "OPEN POs" that looks like this:

 WeekNum Supplier PO number Payment term 33 A 158 30 days 33 C 105 60 days 33 Y 469 30 days 33 A 422 90 days 33 Y 789 75 days 33 U 156 120 days 33 Y 111 45 days 34 A 158 60 days 34 C 105 60 days 34 T 88 90 days 34 Z 156 120 days 34 U 777 120 days 34 B 879 45 days 34 Y 789 45 days 34 U 156 120 days

I'm trying to build a matrix like this:

 Week 34 PO number Supplier From To 158 A 30 days 60 days 789 Y 75 days 45 days

The idea is to show the "PO numbers" that had its "Payment term" changed from one week to another.

The variation always needs to be from the current week (that will be selected on the slicer) versus the previous week.

@mahoneypat ideas? 😁 😬

Thank you for the support! 🙂

1 ACCEPTED SOLUTION
Super User

What should happen in week number 1 ?

Anyway.  Add two calculated columns  (measures are not required here as there is no filter impact)

``````Last Week =
var p='OPEN POs'[PO number]
var s='OPEN POs'[Supplier]
var w='OPEN POs'[WeekNum]
return calculate(max('OPEN POs'[Payment term]),all('OPEN POs'),'OPEN POs'[PO number]=p,'OPEN POs'[Supplier]=s,'OPEN POs'[WeekNum]=w-1)

Different = SWITCH(TRUE(),ISBLANK('OPEN POs'[Last Week]),"no",[Last Week]='OPEN POs'[Payment term],"no","yes")``````

Then filter your table to Different="yes"

7 REPLIES 7
Super User

@edsonlf , refer my blog on Week over a week using week rank. But for that, you need a week and year. In case you only have a week. You can use week in place of week rank.

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Super User

Please try these two expressions for From and To.  They seem to get your desired result.

From = var prevweek = Max(Terms[WeekNum])-1
return CALCULATE(MIN(Terms[Payment term]), Terms[WeekNum] = prevweek)

To = var latestweek = Max(Terms[WeekNum])
return CALCULATE(MIN(Terms[Payment term]), Terms[WeekNum] = latestweek)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User

What should happen in week number 1 ?

Anyway.  Add two calculated columns  (measures are not required here as there is no filter impact)

``````Last Week =
var p='OPEN POs'[PO number]
var s='OPEN POs'[Supplier]
var w='OPEN POs'[WeekNum]
return calculate(max('OPEN POs'[Payment term]),all('OPEN POs'),'OPEN POs'[PO number]=p,'OPEN POs'[Supplier]=s,'OPEN POs'[WeekNum]=w-1)

Different = SWITCH(TRUE(),ISBLANK('OPEN POs'[Last Week]),"no",[Last Week]='OPEN POs'[Payment term],"no","yes")``````

Then filter your table to Different="yes"

Frequent Visitor

Let me try this solution..

btw, week number one can be null.. no reference to bring any variance.

Super User

that's not what I meant.  Is your report limited to a single year?  Or would you expect week 1 look back at week 52 of the prior year etc?

Frequent Visitor

good catch.. at some point i'll have the same week for a subsequent year

Super User

@edsonlf - You should be able to borrow the concept from MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.