topic Re: Monthly sales previous year in function of the current date in DAX Commands and Tips

Monthly sales previous year in function of the current date

joseluis1969 — Thu, 16 May 2019 15:29:18 GMT

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:

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
16/05/2018    85
30/05/2018    120
.    .
.    .
.    .
31/12/2019    95

In the case that the formula is executed on 16/05/2019 (today) its result is:

Year	Month	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 16/05/2018.
NOTE: In power bi I have a calendar table.
Thanks in advance,
José Luis

Re: Monthly sales previous year in function of the current date

parry2k — Sat, 18 May 2019 03:40:24 GMT

joseluis1969 try this measure

sales previous year = 

CALCULATE ( SUM( YourTable[YourColumn] ), DATEADD( Calendar[Date], -12, MONTH))

Re: Monthly sales previous year in function of the current date

joseluis1969 — Mon, 20 May 2019 10:07:34 GMT

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.

Thanks again,

Jose Luis

Re: Monthly sales previous year in function of the current date

parry2k — Mon, 20 May 2019 12:19:26 GMT

joseluis1969 not fully sure what you mean? Can you please explain with example?

Re: Monthly sales previous year in function of the current date

joseluis1969 — Mon, 20 May 2019 16:06:10 GMT

Hello:

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):
Year    Month 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.

Thanks in advance,
José Luis

Re: Monthly sales previous year in function of the current date

parry2k — Mon, 20 May 2019 16:40:00 GMT

joseluis1969 so you expected teh result for may 2019 to be 85 not 215, correct?

Re: Monthly sales previous year in function of the current date

joseluis1969 — Tue, 21 May 2019 07:57:06 GMT

Hello:

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.

José Luis

Re: Monthly sales previous year in function of the current date

parry2k — Tue, 21 May 2019 13:05:28 GMT

joseluis1969 try this measure

sales previous year = 
CALCULATE ( SUM(Table6[Sales_Amount] ),   
DATEADD( FILTER( VALUES('Date'[Date]), 'Date'[Date] <= TODAY() ), -12, MONTH))

Re: Monthly sales previous year in function of the current date

joseluis1969 — Tue, 21 May 2019 14:29:29 GMT

Hello:

The formula works perfectly. Thank you very much for your time and patience.

José Luis