cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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:

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:

YearMonthSales_Amount
2018 1010
 January250
 February210
 March130
 April205
 May215
   

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@joseluis1969 try this measure

 

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





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

@joseluis1969 try this measure

 

sales previous year = 

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





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted

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

Highlighted

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted

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

Highlighted

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted

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

Highlighted

@joseluis1969 try this measure

 

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





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted

Hello:

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

José Luis

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors