Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |