Report to compare Current Values vs Previous Values based on ID

[artlemaks]

Hi.

I am currently working on a report that I want to add to our dashboard.

The report that we are looking for is to compare current value vs previous value by using IDs from the database.

This is what the table looks like

So the ID for current values would be 8027 and for previous values 7995 in the given example. I know this can be achieved with DAX using filtered values, but I haven't been able to find a way to dynamically set the value for the ID for previous values

The DAX calculation that I have used is 

Stock at Cost for 7995 2 = 
CALCULATE(
	SUM('godigitally_easycoun_st view_stocktake_details'[Stock at Cost]),
	'godigitally_easycoun_st view_stocktake_details'[stocktake_id]
		IN { 7995 }
)

The value of 7995 is what needs to change. 

Is DAX calculation the right way to go, or what is the most efficient way of achieving the results that I'm expecting? 

[Tahreem24]

@artlemaks ,

Try the below Column:

Previous Record = CALCULATE(SUM(Table[Cost]),FILTER(Table,Table[DateCOlumn]<EARLIER(Table[DateColumn])))

[artlemaks]

@Tahreem24 same problem as above, it is not allowing me to select the Stocktake Date Column as an option for EARLIER() expression. 

[v-jayw-msft]

Hi @artlemaks ,

This works fine on my side.

Column = CALCULATE(MAX('Table'[id]),FILTER('Table','Table'[stock_date]<EARLIER('Table'[stock_date])))

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

@artlemaks , try new columns

last date = maxx(filter(Table, [store_id]= earlier([store_id]) && [stock date] < earlier([stock date])),[stock date])
last value  = maxx(filter(Table, [store_id]= earlier([store_id]) && [stock] = earlier([last Date])),[ID])

[artlemaks]

Hi @amitchandak .

I have tried to create the column as suggested by you using

last date = maxx(filter('godigitally_easycoun_st stocktake_master', [store_id]= earlier([store_id]) && [Stocktake Date] < earlier([Stocktake Date])),[Stocktake Date])

 But I am getting errors on earlier([store_id]) and earlier([Stocktake Date]). It says

Parameter is not the correct type. Cannot find name '[store_id]'.

 And same error for Stocktake Date. 

Any suggestions?