Week on week values

[dannyboy123]

Hi all. Fairly new to Power BI.  I have a test set of data with 2 locations and data for 2 weeks.  I would like to get the week on week difference by location, but leave it blank if there is no previous week

This is my data

This is what I have...

Measur2 =

    VAR cw = CALCULATE(SUM('Table'[success]),FILTER('Table','Table'[week]=MAX('Table'[week])))

    VAR lw = CALCULATE(SUM('Table'[success]),FILTER('Table','Table'[week]=MAX('Table'[week])-1))

    RETURN IF(ISBLANK(lw), BLANK(), cw-lw)

This works fine if I don't have week selected in the table..

But if I add week, the entire table returns as blank.  

I want something like this...

Thanks 

[Daniel29195 · Accepted solution]

@dannyboy123 

output

Column = 
var w =  'Table'[week]
var depot =  'Table'[depot]

var ds = 
SELECTCOLUMNS(
TOPN(1,
FILTER(
    'Table',
    'Table'[depot] = depot && 'Table'[week] < w
),'Table'[week]
),'Table'[sucess])



var diff =  'Table'[sucess] - ds 

return  IF(ISBLANK(ds) ,BLANK(),diff)

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[dannyboy123]

Hi. Still returns black table if week, depot and measur2 selected

[Daniel29195 · Accepted solution]

@dannyboy123 

output

Column = 
var w =  'Table'[week]
var depot =  'Table'[depot]

var ds = 
SELECTCOLUMNS(
TOPN(1,
FILTER(
    'Table',
    'Table'[depot] = depot && 'Table'[week] < w
),'Table'[week]
),'Table'[sucess])



var diff =  'Table'[sucess] - ds 

return  IF(ISBLANK(ds) ,BLANK(),diff)

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[dannyboy123]

Seems to work just fine, thank you.

It does show an error though...

It says paremeter is not correct type? Why is that?

Thanks again

[Daniel29195 · Accepted solution]

@dannyboy123 

between ),  and  'Table'[success]   ,  write :  "@success" 

the way i wrote it, doesnt work on older power bi version . 

so you need to specify a column name when using selectcolumns . 

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[dannyboy123]

OK, great.  If there was another level of complication, such as if each depot had a sub depot, let's say north and south, how much more difficult would that be?  The success column is a percentage so I wouldn't just want to add up the respective rows and divide, it would need to be based on the total number of parcels eg

[Daniel29195]

@dannyboy123 

Measur2 =

    VAR cw = CALCULATE(SUM('Table'[success]),FILTER(all('Table'),'Table'[week]=MAX('Table'[week])))

    VAR lw = CALCULATE(SUM('Table'[success]),FILTER(('Table'),'Table'[week]=MAX('Table'[week])-1))

RETURN IF(ISBLANK(lw), BLANK(), cw-lw)

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠