Compare current month vs previous month with available month column only

[NPK]

Hi,

help needed with DAX.

i have the fllowing table with product, month and result columns available. But i need to create trend and previous month result columns based on month and result columns using dax. 

Please let me know how to do it.

Thanks

[v-yuezhe-msft]

@NPK,

Create the following columns in your table.

Index = COUNTROWS(FILTER(Table1,Table1[Product]=EARLIER(Table1[Product])&&Table1[Month]<=EARLIER(Table1[Month])))

Previous month result = CALCULATE(FIRSTNONBLANK(Table1[Result],1),FILTER(Table1,Table1[Product]=EARLIER(Table1[Product]) && Table1[Index]=EARLIER(Table1[Index])-1))

Trend = IF(ISBLANK(Table1[Previous month result]),BLANK(), SWITCH(
                          TRUE(),
                         
                  (Table1[Result]-Table1[Previous month result]) <= 0, UNICHAR(128315),
                   (Table1[Result]-Table1[Previous month result]) >= .010,UNICHAR(9650),
              BLANK()
             ))

Regards,
Lydia

[NPK]

Thanks v-yuezhe-msft for your quick response.

I exactly followed your steps, but i'm getting blank rows, am i missing something?

T

Thank you

[v-yuezhe-msft · Accepted solution]

@NPK,

Please review my PBIX file.

https://1drv.ms/u/s!AhsotbnGu1NogywooC50WnB9h2pJ

Regards,
Lydia