Monthly sales previous year in function of the current date
Good afternoon: I need to create a formula that results from the monthly sales (month to month) of the previous year taking into account the current date NOT THE END DATE OF the CURRENT MONTH. I try to better explain my question with an example:
Thank you for your answer. If I use this formula and show the values totaled for years and months, the total of the month of May (current month) of the previous year is that corresponding to 01/05/2018 to 31/05/2018. I need the total from 01/05/2018 to 20/05/2018, that is, from the beginning of the day and current month to the present day of the previous year. The total of the remaining months is correct but the formula does not have to show the total of the months after the current month.
I try to better explain my question with an example: Budget table Date Sales_Amount 01/01/2018 100 01/01/2018 150 01/02/2018 90 05/02/2018 120 04/03/2018 130 05/04/2018 110 20/04/2018 95 01/05/2018 130 20/05/2018 85 30/05/2018 120 . . . . . . 31/12/2019 95 In the case that the formula is executed on 20/05/2019 (today) its result is (pivot table): YearMonth Sales_Amount 2018 1010 January 250 February 210 March 130 April 205 May 215
As you can see the sales of May of the year 2018 (previous year) only include those ranging from 01/05/2018 to 20/05/2018.
The expected result for the month of May 2018 is 215 (130 + 85). The problem is that the dates of the fact table are from 01/01/2018 to 31/12/2019 and if I use the formula that you propose me the result for the month of May is 335 (130 + 85 + 120)in the pivotTable, the result for sales of June 2018 is the summary of the June Sales of the year 2018... In the PivotTable. I only need the results of the months January, February, March, April and May of the year 2018 and the results of May of the year 2018 only have to add the data from 01/05/2018 to 21/05/2018 (current day and month),the expected result for the month of May 2018 is 215 . I apologize if I'm not explaining myself well. Thank you very much for your patience and time.