cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dmorala2
New Member

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.

2 ACCEPTED SOLUTIONS
V-lianl-msft
Community Support
Community Support

Hi @dmorala2 ,

 

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

V-lianl-msft_0-1610005996203.png

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.

View solution in original post

dmorala2
New Member

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]
 
 

View solution in original post

3 REPLIES 3
dmorala2
New Member

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]
 
 

View solution in original post

V-lianl-msft
Community Support
Community Support

Hi @dmorala2 ,

 

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

V-lianl-msft_0-1610005996203.png

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@dmorala2 , 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors