Getting Prev Row Value in dynamic Table

[Anonymous]

I'm trygin to do some change analysis and so need to calculate the change from previous dates.  I've seen several examples of ding this where the data is a "convenient" format, already partially summarised, but that won't work for this scenario as the data might be sliced other ways.

So I have table something like:

Date	REF	Expected	Actual
01/01/2020	AFA	2	3
01/01/2020	SSF	3	4
04/01/2020	AWE	4	4
08/01/2020	DAQ	4	3
08/01/2020	DWA	1	2
09/01/2020	ASW	2	3

There are repeated dates, and missing dates.

I want to compare Actual to Expected to calculate a FACTOR for each row, and I've done this with both measures and calculated columns ok.  

Date	REF	Expected	Actual	FACTOR
01/01/2020	AFA	2	3	1.5
01/01/2020	SSF	3	4	1.33
04/01/2020	AWE	4	4	1
08/01/2020	DAQ	4	3	.75
08/01/2020	DWA	1	2	2
09/01/2020	ASW	6	3	0.5

Next I want to see the average FACTOR for each day in the data, and to do that I've create a Meaure,

AVF = calculate(average('tblPackets'[propTime]))

...which works fine:

Date	AVF
01/01/2020	1.41
04/01/2020	1
08/01/2020	1.38
09/01/2020	0.5

 HERE'S WHERE I'M STUCK :o(

I now want to compare the AVF with the previous row to see what the trend is, but so far I cannot create the following requried table:

Date	AVF	PrevAVF
01/01/2020	1.41
04/01/2020	1	1.41
08/01/2020	1.38	1
09/01/2020	0.5	1.38

Of course, once this is done I want to then go another step to Divide AVF with PrevAVF, and finally sum those numbers, but I think this is the sticking point on that journey.

Like I said, most examples I've found already have the data in this kind of strucutre, but as this table is already derived, those tactics don't work.  Is this possible ???

[tuliomelibeu]

EnateJon,

First i created the FACTOR measure:

Factor =
var vTotal_Expected = SUM(factData[Expected])
var vTotal_Actual = SUM(factData[Actual])
return
DIVIDE(
vTotal_Actual,
vTotal_Expected
)

Then i created the AVF measure:

AVF =
AVERAGEX(
  factData,
  [Factor]
)

Then i created the "AVF Previous" measure:
AVF previous =
var vActualDate = MAX(factData[Date])
var vDate_Previous =
CALCULATE(
  MAX(factData[Date]),
  FILTER(
    ALL(factData[Date]),
    factData[Date] < vActualDate
  )
)
var vAVF_Date_Previous =
CALCULATE(
  [AVF],
 FILTER(
    ALL(factData[Date]),
    factData[Date] = vDate_Previous
  )
)
return
vAVF_Date_Previous

And finally i created the "Var % AVF" measure:

Var % AVF =
DIVIDE(
  [AVF] - [AVF previous],
  [AVF previous]
)

Here is the image with the solution

Here is the PBIX file

att,

Tulio Melibeu

[Anonymous]

This is great stuff - thank you.  Just one step away from final answer, and I've failed to get there :o(

The last step is to calculate (and show on a card) the AVERAGE of all the values of the "Var % AVF" to see if it's rising or falling.

I've tried AVERAGE but it will only take a column (not a meaure?) and AVERAGEX(table, [Var % AVF]) doesn't return anything.

So close....

[v-easonf-msft]

Hi， @Anonymous 

Not fully sure what is your  last question, can you elaborate a bit more?

Provide some screenshots to explain what you are trying to do.

It will be  better if you can show your excepted  result about the  AVERAGE of all the values of the "Var % AVF" .

Best Regards,
Community Support Team _ Eason

[tuliomelibeu · Accepted solution]

Ok...

Average var % =

AVERAGEX(

  VALUES(factData[Date]),

  [Var % AVF]

)

Download the PBIX File

[Anonymous]

This is the only part of the solution I haven't been able to follow.   Intuitively i would have written:
      Average var %=AVERAGEX(factData,  [Var % AVF])

But that doesn't return anything (I can't see why?).

What does "Values(table[field])" do, and why is it needed here?

[Anonymous]

Thanks so much for this.  I'm quite new to DAX (as you might have guessed) and I'm not finding it comletely intuitive.  Lookoing at what you've suggested I can begin to reverse engineer these - really helpful.  Thanks again.

[Greg_Deckler]

@Anonymous - Not sure why AVERAGEX across your table variable would not work. More or less a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Greg_Deckler]

@Anonymous - You should be able to create a table variable in a measure and use ADDCOLUMNS coupled with EARLIER. You can get a sense of it here. 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

Obviously that is implemented as columns but it's the same thing when you create and work with a table variable in DAX.

[amitchandak]

@Anonymous , try with date calendar

Last Day Non Continuous = CALCULATE(sum(Table[Actual]),filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

refer both column and measure approach in blog

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9