Difference between previous week

[cham]

Hi,

Here is my data set,

Date	Spend	Pay
22-07-19	589.32	425.22
24-07-19	3652.85	1833.72
21-07-19	3659.5	2744.64
24-07-19	2312.59	1726.67
24-07-19	519.59	347.25
24-07-19	21178.48	17955.15
24-07-19	3016.45	3734.56
22-07-19	2130.75	1562.65
31-07-19	107.52	68.16
31-07-19	65	45.5
31-07-19	1430.74	1078.24
31-07-19	108.3	15534.32
01-08-19	1011.23	10910.91

I created a mesaure using this data set as below,

%Amount = (SUM('Sheet1'[Spend]) - SUM('Sheet1'[Total Pay])) / SUM('Sheet1'[Spend])

I want to get the diffrence between previous week %Amoun with this week. How can i do that?Hope this will help you because i provide the data set.

[Anonymous]

Hey @cham 

You can use a calculated column:

% = CALCULATE(((SUM(T20[Spend]) - SUM(T20[Pay])) / SUM(T20[Spend])),
FILTER(T20, T20[WEEKNUM] = EARLIER(T20[WEEKNUM])))

Where WEEKNUM is another column:

WEEKNUM = WEEKNUM(T20[Date ])

I did NOT QA this solution.

Thanks!
A

[cham]

Hi @Anonymous ,

What is this T20 you mentioned in your measure? Can you please explain it to me?

[Anonymous]

@cham 

Thats the table name.

In your case it is Sheet1

Thansk!
A

[cham]

Hi @Anonymous ,

This is not working see below pic,

Could you please help me to sort this?

[Anonymous]

@cham 

You'll have to create a column, not a measure.

Cannot use EARLIER in a measure.

But, you can find ways to workaround this.

Thanks!
A

[cham]

Hi @Anonymous ,

Still its giving me an error,

[cham]

Hi @Anonymous ,

I want to create a mesuare because this one is not wokring if we create it as column. I already creted a amont% in measure.

Do you know how to get the differenece between previous week using measure?

[Anonymous]

@cham 

Try adding the WEEKNUM and use it in your calculations.

That's the basic idea.

Thanks!
A

[Anonymous]

@cham 

You are missing a bracket after 

SUM(T20[Pay]))

Or other syntax issue.

Pay attention you have all barackets and other characters in place.

Thanks!
A

[Anonymous]

Hi @cham ,

Check if this helps.

https://community.powerbi.com/t5/Desktop/Variance-over-week-DAX/m-p/479504#M223174

Cheers

CheenuSing