cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Current Quarter and last quarter calculation

Dear Team, need some help.

 

I have a Sales amount ranging from May 30' 2019 till Jan 01' 2017. How do I get the current quarter total sales and last quarter total sales? the previous quarter formula does not work. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Current Quarter and last quarter calculation

Hi @shilpikumari_11 ,

 

You can create Quarter column first of all.

 

Quarter = ROUNDUP(MONTH(Table1[Date])/3,0)

 

Then create measures to get the current quarter total sales and last quarter total sales.

 

Total sales_current quarter = CALCULATE(SUM(Table1[sale]),FILTER(ALLSELECTED(Table1), Table1[Quarter] =MAX(Table1[Quarter])))

 

Total sales_last quarter = CALCULATE(SUM(Table1[sale]),FILTER(ALLSELECTED(Table1), Table1[Quarter] =MAX(Table1[Quarter])-1))

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Highlighted
Super User I
Super User I

Re: Current Quarter and last quarter calculation

Hi @shilpikumari_11 ,

 

You can make this small change to the Quarter computation

 

Quarter =Year(Table1[Date])*4 +  ROUNDUP(MONTH(Table1[Date])/3,0)

 

 

What this will result is a running serial number for the quarters  from  8069 for Jan - March 2017 - 1st quarter and so on until 8079 - for Apr-May 2019 - 2 quarter.

 

Use the formula as suggested by @v-xicai  for current quarter and previous quarter. It will work.

 

Check it out.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Current Quarter and last quarter calculation

Hi @shilpikumari_11 ,

 

You can create Quarter column first of all.

 

Quarter = ROUNDUP(MONTH(Table1[Date])/3,0)

 

Then create measures to get the current quarter total sales and last quarter total sales.

 

Total sales_current quarter = CALCULATE(SUM(Table1[sale]),FILTER(ALLSELECTED(Table1), Table1[Quarter] =MAX(Table1[Quarter])))

 

Total sales_last quarter = CALCULATE(SUM(Table1[sale]),FILTER(ALLSELECTED(Table1), Table1[Quarter] =MAX(Table1[Quarter])-1))

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Highlighted
Helper III
Helper III

Re: Current Quarter and last quarter calculation

@v-xicai 

 

Thanks, Amy for replying, actually below formula did not work reasons being.

1. The quarter formula converts as text by default but when I converted it to whole number it works.

2. now the max quarter gives me 4 but right now our max quarter is Q2, 2019. so the total sales values are coming wrong. Similarly, for last qaurter, it just gives 3. Maybe we need to fixed it with Year as well, but I tried and it does not work.

 

 

Any thoughts. Thanks.

 

Highlighted
Super User IV
Super User IV

Re: Current Quarter and last quarter calculation

Hi,

Try this:

  1. Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table
  2. In the Calendar Table, write this calculated column formula to extract the Quarter: Quarter = IF(Month('Calendar'[Date])<=3,"Q1",IF(Month('Calendar'[Date])<=6,"Q2",IF(Month('Calendar'[Date])<=9,"Q3","Q4"))).  Write another calculated column to extract the Year: Year = Year('Calendar'[Date])
  3. To your visual, drag Year and Quarter from the Calendar Table
  4. Write these measures

Total sales = SUM(Data[Sales])

Total sales in previous quarter = CALCULATE([Total sales],PREVIOUSQUARTER('Calendar'[Date]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper III
Helper III

Re: Current Quarter and last quarter calculation

@Ashish_Mathur  Thanks.

 

I am not a using year and a quarter on my visual, it just a card that shows last quarter sales and in another card it shows current quarter sales.

 

I have tried the previous quarter formula by fixing it at the calendar date column, does not work.

Highlighted
Super User I
Super User I

Re: Current Quarter and last quarter calculation

Hi @shilpikumari_11 ,

 

You can make this small change to the Quarter computation

 

Quarter =Year(Table1[Date])*4 +  ROUNDUP(MONTH(Table1[Date])/3,0)

 

 

What this will result is a running serial number for the quarters  from  8069 for Jan - March 2017 - 1st quarter and so on until 8079 - for Apr-May 2019 - 2 quarter.

 

Use the formula as suggested by @v-xicai  for current quarter and previous quarter. It will work.

 

Check it out.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Current Quarter and last quarter calculation

Hi,

So if the Date column in your Data Table is till June 3, 2019, the the current quarter's revenue will be from April 1, to June 3.  Am i correct?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors