Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joseluis1969
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

@joseluis1969 try this measure

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

@joseluis1969 try this measure

 

sales previous year = 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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

@joseluis1969 try this measure

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Hello:

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

José Luis

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors