Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate difference in values from the current week

Hello

I would like to calculate by formula, the difference in "Calls..." either in values or in %, from last week - the previous one, by category.

For example with this last week (53_20)-(52_20).

NOTE: I have an index that sorts those weeks chronologically.

dmorala2_0-1609844739372.png

Thanks in advance.

  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    Unfortunately, the picture is too vague to provide valid information.

    Based on your description, I have created a test file for your reference. The measure is as follows:

    Measure = 
var this_index = MAX('Table'[Index])
var last_week = CALCULATE(
    SUM('Table'[value]),
    FILTER(ALLEXCEPT('Table','Table'[category]),'Table'[Index]=this_index-1))
var this_week = SUM('Table'[value])
return this_week-last_week

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Gracias a los 2,

     

    pero solucionado!

     

    LastW = CALCULATE(sum('WEEK Skyline'[Calls SK]), FILTER(ALLSELECTED('WEEK Skyline'), 'WEEK Skyline'[INDICE]=max('WEEK Skyline'[INDICE])-1))
    ThisW = CALCULATE(sum('WEEK Skyline'[Calls SK]), FILTER(ALLSELECTED('WEEK Skyline'), 'WEEK Skyline'[INDICE]=max('WEEK Skyline'[INDICE])))
     
    ThisVSLast = [ThisW] - [LastW]
     
     

3 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Unfortunately, the picture is too vague to provide valid information.

    Based on your description, I have created a test file for your reference. The measure is as follows:

    Measure = 
var this_index = MAX('Table'[Index])
var last_week = CALCULATE(
    SUM('Table'[value]),
    FILTER(ALLEXCEPT('Table','Table'[category]),'Table'[Index]=this_index-1))
var this_week = SUM('Table'[value])
return this_week-last_week

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Gracias a los 2,

     

    pero solucionado!

     

    LastW = CALCULATE(sum('WEEK Skyline'[Calls SK]), FILTER(ALLSELECTED('WEEK Skyline'), 'WEEK Skyline'[INDICE]=max('WEEK Skyline'[INDICE])-1))
    ThisW = CALCULATE(sum('WEEK Skyline'[Calls SK]), FILTER(ALLSELECTED('WEEK Skyline'), 'WEEK Skyline'[INDICE]=max('WEEK Skyline'[INDICE])))
     
    ThisVSLast = [ThisW] - [LastW]
     
     
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , In case you need measure, Create a date or week table (separate) with week rank and use that

    new column

    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense)

     

    measure
    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8