Previous week value same row

[Anonymous]

Hi,

i have a table with list of different categories and values by weekly.

as default dates are with week difference.

i need to show the previous week value along with the current week value in the table.

below is example data. for an instance if i have 23102 for 2/14/2006, i need to show what is the value for 2/7/2006.

if i do this it is summing up entire dataset

last7days =

Var PreWeek = Append1[OPR_DATE]-7

Return

CALCULATE(FIRSTNONBLANK(Append1[VALUE],Append1[VALUE]),FILTER(Append1,Append1[OPR_DATE]=PreWeek))

2/7/2006	WHEAT	Non-Commercial	CBT	48924
2/7/2006	WHEAT	Non-Commercial	KCBT	54586
2/7/2006	WHEAT	Non-Reportable	CBT	55086
2/7/2006	WHEAT	Non-Commercial	CBT	65702
2/7/2006	WHEAT	Non-Commercial	CBT	93391
2/14/2006	WHEAT	Non-Reportable	CBT	5741
2/14/2006	WHEAT	Non-Commercial	KCBT	23102
2/14/2006	WHEAT	Non-Reportable	KCBT	33330
2/14/2006	WHEAT	Non-Reportable	KCBT	34973
2/14/2006	WHEAT	Non-Reportable	CBT	41912
2/14/2006	WHEAT	Non-Commercial	KCBT	51494
2/14/2006	WHEAT	Non-Commercial	CBT	53453
2/14/2006	WHEAT	Non-Reportable	CBT	60827
2/14/2006	WHEAT	Non-Commercial	CBT	67174
2/14/2006	WHEAT	Non-Commercial	CBT	87459
2/21/2006	WHEAT	Non-Commercial	CBT	14811
2/21/2006	WHEAT	Non-Commercial	KCBT	26683

@Pragati11 @amitchandak @Greg_Deckler @selimovd 

[Greg_Deckler · Accepted solution]

@Anonymous Seems like a variation on MTBF. 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/339586.
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

[v-xiaotang · Accepted solution]

Hi @Anonymous 

you can try this measure,

Measure = 
var _lastWeek=MIN(Append1[OPR_DATE])-7
return CALCULATE(SUM(Append1[VALUE]),FILTER(ALLEXCEPT(Append1,Append1[WHEAT],Append1[Non],Append1[Category]),Append1[OPR_DATE]=_lastWeek))

result

if you need column,

Column = 
var _lastWeek=Append1[OPR_DATE]-7
return CALCULATE(SUM(Append1[VALUE]),FILTER(ALLEXCEPT(Append1,Append1[WHEAT],Append1[Non],Append1[Category]),Append1[OPR_DATE]=_lastWeek))

result

Best Regards,

Community Support Team _Tang

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

[Greg_Deckler · Accepted solution]

@Anonymous Seems like a variation on MTBF. 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/339586.
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

[Anonymous]

@Greg_Deckler thank you for your time.