How to compare data current vs previous in a dynamic table without time period?

[Anonymous]

Hi,

As a newbie to pbi & dax basics, i need help to create a formula comparing current data vs previous data with specific filters applied (i.e. a customer's last order vs previous order with same filters like from/to location and even commodity type). Dataset will be updated daily, not real-time query.  Any suggestion? 

Thanks, 

[amitchandak]

@Anonymous , A new column like this

New column =
var _last = maxx(filter(Table, [Customer_id] = earlier([Customer_id]) && [Order] < earlier([Order])),[Order])

return
sumx(filter(Table, [Customer_id] = earlier([Customer_id]) && [Order] _last),[value])

or measure like

New Measure =
var _last = maxx(filter(allselcted(Table), [Customer_id] = max([Customer_id]) && [Order] < max([Order])),[Order])

return
sumx(filter(allselcted(Table), [Customer_id] = max([Customer_id]) && [Order] _last),[value])

[Anonymous]

Thank you for your answer but i probably do sth wrong, in your measure syntax "_last" raises error as unexpected expression, 

Here's my version after putting tables in your measure; 

New Measure = var _last = maxx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [TEU] < max([TEU])),[TEU])

return

sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [TEU] _last),[TEU])

*i assume that you set this measure up considering unique order IDs, but in my dataset there's no order IDs, instead i should use order dates to compare last vs previous order. 

*one minor thing, at the end of your measure, what should be in "value" table, i'm a bit confused while selecting right table for it. 

Thanks again for your help

[V-pazhen-msft · Accepted solution]

@Anonymous 
The [Value] is the data field(e.g. sales) you want to compare, for example if you want to compare the sum sales between current order date and previous order date:

The measures should be something like the following.

Previous data = Sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [OrderDate] < max([OrderDate])),[Data])))
Current data = Sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [OrderDate] = max([OrderDate])))

Then you can just create a measure to compare:
Measure = current data - previous data

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.