Current Quarter and last quarter calculation

[Anonymous]

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. 

[v-xicai · Accepted solution]

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.

[474Raghad]

Hi Team, 

I've same question i want to display the data for the current year and current quarter + the past 3 quarters for example if we are in 2025 and the current quarter is Q1 i want to display the period as 2025 Q1 and 2024 Q4, Q3 and Q2. i want to esnure that this code is sustanaible for every quarter. 

[Ashish_Mathur]

Hi,

If you want to show data of each quarter as opposed to adding up the numbers of each quarter, then refer to my solution in the attached files.

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

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?

[v-xicai · Accepted solution]

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.

[hxu]

I appreciate your genius solution!!

[moatazezat]

Dears, 

Could you please answer 

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

[CheenuSing · Accepted solution]

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

[Anonymous]

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]

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