Power Query/Power Pivot Conditional Column creation & chart

Hi

Please see the attached file and see below the steps I would like to achieve in this file by power query or power pivot so it is automated when a new month data comes in monthly. I need these if conditions to create a red, yellow, and green dynamic pivot chart. I would really appreciate your help in achieving it.




After the above steps, pivot chart to create example below:

To calculate the average for the most recent three months, here is an example. I used greater thatn 30 days and less than 120 days from today. You will need to adjust this to exactly what you want. All of your data is for periods greater than a month old. It is beneficial to us that are offering help that your requests are realistic to the data provided to test.

Please Login or Register  to view this content.

Hi Alan,

Thank you so much for your reply and taking time out. I really appreicate. Would it be possible if you could do this in the file i provided, since kinda lost. Also, confused on greater than 30days and 120 days sinceI want to calculate the average for last 3 months, dynamically (pivot table), which means with every new month and rate added in the table, it automatically adjusts the periods to calculate the average for last 3 months excluding the current month. . I basically need the exact results below since when I did it it, it didn't work and also the conditions didn't work at all. Please see the exact steps I need to acheive below:

Exact steps for the results need to be in a pivot table format:

1. I have 2 columns in the attached file, Month and Rate. I want to get the previous 3 months average in a column(excluding the current month) so For example in the file for month and rate column:
for the current month 20-Oct -(72%), I want only the previous 3 months rate average for 20Sept-(79%), 20Aug-(76%), 20Jul-(85%) .

2. After the 1st step above, populate percentages in the columns for if conditions or NA.

-If last 3 months average is greater than or equal to current month then populate the percentage or NA. (GREEN IF CONDITION)
-if the 3 months average for previous months less than the current month then (RED IF CONDITION)
-If greater than or equal to 95% to 100% (YELLOW IF CONIDTION)

Sorry, I have been playing with this for a couple of hours and cannot get a valid solution to determine latest three months. I don't really understand the second condition. However if you want to use =Averageifs function, then convert your table to a range and place this formula in C2 =AVERAGEIFS(Source!$B$2:$B$398,Source!$A$2:$A$398,">=8/1/2020",Source!$A$2:$A$398,"<=12/1/2020")

FYI, I don't work with Graphs/charts so I am no help there.

Good Luck with finding a solution that works for you.

Perhaps, considering several days have past, a formula based (partial) solution may help.
1. The months are listed using: =MIN(Table1[Date]) and =EDATE(F4,1)
2. The percentage of the current month is displayed using: =AVERAGEIFS(Table1[Rate],Table1[Date],">="&F4,Table1[Date],"<="&EOMONTH(F4,0))
3. The percentage of the previous three months is displayed using: =IF(OR(ISTEXT(G1),G1=""),NA(),AVERAGE(G1:G3))
4. The chart is a line graph with markers with a RAG background, however I don't know how to make the background dynamic (thus this is a "partial" solution).
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.