Unable to display a url image based on previous month value

[reymard]

Hi coummunity,

I'm currently struggling to display customized images based on the comparison between current and previous month value..

My need is pretty simple : 

- I have a Value table with the following structure

Year_Month | Current_Month_%

201601 | 0.75

201602 | 0.85

201603 | 0.78

201604 | 0.86

...

- I have a Date table with a relationship on the column Year_Month and a proper Date column

- I would like to add a customized column in order to store the previous month % for each month, and another calculated column to store images url (green and red arrows)

Year_Month | Current_Month_% | Previous_Month_% | URL_Arrow

201601 | 0.75 | 0.68 | URL_ARROW_DOWN

201602 | 0.85 | 0.75 | URL_ARROW_DOWN

201603 | 0.78 | 0.85 | URL_ARROW_UP

201604 | 0.86 | 0.78 | URL_ARROW_DOWN

...

My problem is when I tried to create the previous month column.. I tried to use this DAX formula but it's not working : Previous_Month_% = CALCULATE(SUM(Current_Month_%),PREVIOUSMONTH(Date_Table[Date]))

I also tried with this one, but it's not a success : 

Previous_Month_% = SUMX(FILTER(ALL(Value_Table),[Date].[MonthNo]=EARLIER(Date_Table[Date].[MonthNo])-1),Value_Table[Current_Month_%])

Do you have any idea ?

Thank you for your help.

[v-huizhn-msft · Accepted solution]

Hi @reymard,

Please add a rank column using the formula below.

rank=RANKX('ValueTable','ValueTable'[ear_Month],,ASC)

Then create a calculated column using the formula below to get previous month % for each month.

Previous_Month_% =
LOOKUPVALUE ( [Current_Month_%], [rank], [rank] - 1 )

Best Regards,
Angelia

[reymard]

Hi coummunity,

I'm currently struggling to display customized images based on the comparison between current and previous month value..

My need is pretty simple :

- I have a Value table with the following structure

Year_Month | Current_Month_%

201601 | 0.75

201602 | 0.85

201603 | 0.78

201604 | 0.86

...

- I have a Date table with a relationship on the column Year_Month and a proper Date column

- I would like to add a customized column in order to store the previous month % for each month, and another calculated column to store images url (green and red arrows)

Year_Month | Current_Month_% | Previous_Month_% | URL_Arrow

201601 | 0.75 | 0.68 | URL_ARROW_DOWN

201602 | 0.85 | 0.75 | URL_ARROW_DOWN

201603 | 0.78 | 0.85 | URL_ARROW_UP

201604 | 0.86 | 0.78 | URL_ARROW_DOWN

...

My problem is when I tried to create the previous month column.. I tried to use this DAX formula but it's not working :

Previous_Month_% = CALCULATE(SUM(Current_Month_%),PREVIOUSMONTH(Date_Table[Date]))

I also tried with this one, but it's not a success : 

Previous_Month_% = SUMX(FILTER(ALL(Value_Table),[Date].[MonthNo]=EARLIER(Date_Table[Date].[MonthNo])-1),Value_Table[Current_Month_%])

Do you have any idea ?

Thank you for your help.