Forum Discussion

dannyboy123's avatar
dannyboy123
Frequent Visitor
2 years ago
Solved

Week on week values

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 

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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! 🤠

  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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! 🤠

6 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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! 🤠
        
  • dannyboy123's avatar
    dannyboy123
    Frequent Visitor
    2 years ago

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

    • Daniel29195's avatar
      Daniel29195
      Community Champion
      2 years ago

      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's avatar
        dannyboy123
        Frequent Visitor
        2 years ago

        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