Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
@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.
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |