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
Anonymous
Not applicable

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
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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

Hi @Anonymous ,

 

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

9 REPLIES 9
Ashish_Mathur
Super User
Super User

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/
Anonymous
Not applicable

@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.

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/
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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.

 

I appreciate your genius solution!!

Dears, 

Could you please answer 

calculate = (Current Quarter Current Year) and (Last Quarter Previous Year)

Hi @Anonymous ,

 

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!
Anonymous
Not applicable

This solved the issue of having quarters from different years. 
Ex: Q1-2019 & Q1-2020.  Using the solution marked in this post, will result in a overlap of both values from these quarters, since we're only looking for the Quarter number. By creating a serialized number, we avoid this overlap, since each quarter has a unique key. 

Anonymous
Not applicable

@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.

 

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.