Dynamic measure for dividing revenues of a week value against its previous week

[venetsa]

Hi everyone,

I need to calculate the percentage change of  the "Fatturato" column from a week against its previous week

(i.g. If today's current week is 01 so I will need % change of week 01 against 53, then week 53 against 52 and so on).

Next week is 02 so I will need to display the percentage change of week 02 against 01 and so on.

I have a table which shows the last 4 weeks values.
The table has three columns:
1) the first column is a string representing weeks
2) the second column has revenues
3) the third column is a text string which defines the current week as "Ultima settimana chiusa", the previous week as "Settimana chiusa -1" and so on until the 4th week. The column "O_Flag Ultime settimane" changes as the weeks goes on: if we were in week 53 its corresponding value in column "O_Flag Ultime settimane" would be "Ultima settimana chiusa" .

I cannot add colums to the tables.

here a screenshot of the table:

Can you help me?

Thank You

[v-jayw-msft]

Hi @venetsa ,

Sorry for the delay, please refer the following measures:

Current week Fatturato = calculate(sum('table'[Fatturato]),filter('table','table'[Settimana Ordine] = selectedvalue('table'[Settimana Ordine])))

previous week Fatturato = 
var _week = mid(selectedvalue('table'[Settimana Ordine]),1,2)
var _year = mid(selectedvalue('table'[Settimana Ordine]),10,4)
var _previous = calculate(sum('table'[Fatturato]),filter(all('table'),mid('table'[Settimana Ordine],1,2) =_week-1&&mid('table'[Settimana Ordine],10,4)= var _year))
var _week01 = calculate(sum('table'[Fatturato]),filter(all('table'),mid('table'[Settimana Ordine],1,2) = 53&&mid('table'[Settimana Ordine],10,4)= var _year)))
return
if(var _week= 01,_week01,_previous)

Best Regards,

Jay

[venetsa]

Hi @v-jayw-msft ,

I've tried your formula and it looks like the syntax is not correct with brackets at the end of the var_previous line

previous week Fatturato =

var _week = mid(SELECTEDVALUE('Data Ordine'[Settimana Ordine (Date)]),1,2)

var _year = mid(selectedvalue('Data Ordine'[Settimana Ordine (Date)]),10,4)

var _previous = calculate(Ordinato[Importo Cliente PC],filter(all('Data Ordine'),mid('Data Ordine'[Settimana Ordine (Date)],1,2)=_week-01))&&mid(SELECTEDVALUE('Data Ordine'[Settimana Ordine (Date)]),10,4)= var _year))

var _currentweek = calculate(Ordinato[Importo Cliente PC],filter(all('Data Ordine'),mid('Data Ordine'[Settimana Ordine (Date)],1,2))) = _week&&mid('Data Ordine'[Settimana Ordine (Date)],10,4)= var _year))

RETURN

IF(_week=01, _currentweek, _previous)

I tried correcting as suggested by PowerBi but still get an error

[v-jayw-msft]

Hi @venetsa ,

Please delete the two brackets in the place marked in the screenshot.

Best Regards,

Jay

[v-jayw-msft]

Hi @venetsa ,

You could get the weeknum and year by using MID() function . Then for current row, find out the year = current row year and weeknum = current row weeknum-1(Specify week 53 for week 01). 

Best Regards,

Jay

[BaronSdG]

Hi @v-jayw-msft ,

Can you clarify how that works and provide a formula? We tried creating a measure where we use mid but it returned an error

middate = MID('Data Ordine'[Settimana Ordine (Date)],18,10)

'A single value for column settimana ordine [date] in table data ordine cannot be determined. 

We then tried adding MAXX before mid but it still returned an error

Thanks

[Greg_Deckler]

@venetsa See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[BaronSdG]

Hi @Greg_Deckler ,

Any news on this?

Thanks

[venetsa]

Hi @Greg_Deckler ,

I have tried your formula but I have some errors.

Prova % prev week =

VAR current2= [Fatturato B2C]

VAR previous_Date = MAXX(FILTER('Data Ordine', 'Data Ordine'[Settimana Ordine]<EARLIER('Data Ordine'[Settimana Ordine])),'Data Ordine'[Settimana Ordine])

VAR previous = MAXX(FILTER('Data Ordine', 'Data Ordine'[Settimana Ordine]= previous_Date), [Fatturato B2C])

RETURN

current2-previous

Translating: EARLIEST refers to an earlier row context that doesn't exist.

Also does this formula works even if the column it is using as reference is the first column? I also tested by using the MID function to extract only the latest date for that string.

Thanks

[PaulDBrown]

Do you have a date table?