Search code examples
excelvbaexcel-formulacustom-functions-excel

Find the last semester using Function LastQuarter + 2

I need a code that will count how many months was active in the past semester.

I have a pivot and the data changes all the time when the pivot is refreshed, However, I need to calculate the last 6 months average but only using the months that was active in the last semester. e.g enter image description here in this case I would take the average for the last 6 months will only use 4 months (2020 Jan, Feb, Mar & 2019 Dec) because it is the months there person was active in the last semester).

The rule is the semester start in the month we are now (April) and it goes back 6 months. Apr2020, Mar2020,Feb2020,Jan 2020 & Dec2019,Nov2019 and from this months we would take the amount only for the 4 months the client as active like in the image

would it be possible to use 

Public Function LastQuarter(theDate As Date) As Date + 2 months to get the last semester? 
DateSerial(Year(theDate), 2)+2

However, I am guessing I need to use IF the last 6 months is = 6 active months in a row then go to code that is already done

else

But IF the last 6 months is < 6 active months in a row Then count the number of active months in the last semester (using the Function LastQuarter(theDate As Date) As Date + 2 months) and with the total amount of this active months and do the average of the values. cells(number of active months, 3).value ="Average"

ps: I posted a similar question on https://www.ozgrid.com/forum/index.php?thread/1227330-how-to-calculate-how-many-months-is-active-in-a-semester/#wcf9?

Solution

  • You can use the GETPIVOTDATA function.

    Assuming you do not have more than one year of data in the table, given the workbook you downloaded at the other website, for the Amount average for the previous six existing months:

    =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    For a non-existent month, the formula returns an error. The IFERROR function converts that to a null string, which will be ignored by the AVERAGE function.

    You can make the obvious changes for Averaging the Bene Frequency column, and extending the array constant to include 12 months instead of 6

    If you might have more than 12 months in the Pivot Table, then you need to check for the year also:

    =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0})),"Years",YEAR(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    enter image description here