how to query new items from previous month

[WJPHANG]

how do i create a *new measure that indicates the change from previous month for example, a new item or increase/decrease in amount for the month of June versus March?

Data available as below:

Date                               Items                   Amount           *new measure

01 March 2016               Apples                10               

01 March 2016              Banana                 20

01  June 2016                Pineapple             5                            New                   

01  June 2016                Banana                 30                         50% 

01 June 2016                 Apples                  8                           -20%

Thanks in advance

[v-sihou-msft · Accepted solution]

@WJPHANG

In this scenario, you need to get previous date first, then use it to lookup previous amount for your calculation.

Please create a calculated column like below:

Change Rate = 
var PreviousDate=CALCULATE(MAX(Fruit[Date]),FILTER(Fruit,Fruit[Items]=EARLIER(Fruit[Items]) && Fruit[Date]<EARLIER(Fruit[Date])))
var PreviousAmount=LOOKUPVALUE(Fruit[Amount],Fruit[Date],PreviousDate,Fruit[Items],Fruit[Items])
return 
IF(ISBLANK(PreviousAmount),BLANK(),(PreviousAmount-Fruit[Amount])/PreviousAmount)

Regards,

[cs_skit]

You need a Rank grouped by item ordered by date then you can reference to that rank -1 to compare with values from last month its almost the same solution as here:

http://community.powerbi.com/t5/Desktop/Last-country-visited-timeline-logic/m-p/206028#M90786