cancel
Showing results for
Did you mean:
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:

 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.
José Luis

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

@joseluis1969 try this measure

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

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

8 REPLIES 8
Highlighted
Super User IV

@joseluis1969 try this measure

```sales previous year =

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

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

Highlighted
Frequent Visitor

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
Super User IV

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

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

Highlighted
Frequent Visitor

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.

José Luis

Highlighted
Super User IV

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

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

Highlighted
Frequent Visitor

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
Super User IV

@joseluis1969 try this measure

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

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

Highlighted
Frequent Visitor

Hello:

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

José Luis

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors