cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newgirl Frequent Visitor
Frequent Visitor

Quarter Formulas

Hi

I need to make an additional page in my report showing QUARTER, vs PREVIOUS QUARTER and vs LAST YEAR'S SAME QUARTER.

 

Below is a sample data set and I added a new column that would compute the corresponding quarter the month is in using 

QUARTER = "Q"&ROUNDUP(DIVIDE(MONTH('test augh'[Date]),3),0) 
 
LASTNICKNAMEMIDDLEFIRSTAmountDate
MAGDKSANDANA5Jan-19
AGURICHIEGIMRICCHARD3Jan-19
MAGDKSANDANA1Feb-19
AGURICHIEGIMRICCHARD5Feb-19
MAGDKSANDANA20Mar-19
AGURICHIEGIMRICCHARD9Mar-19
MAGDKSANDANA10Apr-19
AGURICHIEGIMRICCHARD30Apr-19
MAGDKSANDANA5May-19
AGURICHIEGIMRICCHARD8May-19
MAGDKSANDANA25Jun-19
AGURICHIEGIMRICCHARD8Jun-19
MAGDKSANDANA200Jul-19
AGURICHIEGIMRICCHARD5Aug-19
MAGDKSANDANA7Sep-19
AGURICHIEGIMRICCHARD10Oct-19
MAGDKSANDANA23Oct-19
AGURICHIEGIMRICCHARD4Nov-19
MAGDKSANDANA12Nov-19
AGURICHIEGIMRICCHARD10Dec-19
MAGDKSANDANA5Jan-18
AGURICHIEGIMRICCHARD3Jan-18
MAGDKSANDANA1Feb-18
AGURICHIEGIMRICCHARD5Feb-18
MAGDKSANDANA20Mar-18
AGURICHIEGIMRICCHARD9Mar-18
MAGDKSANDANA10Apr-18
AGURICHIEGIMRICCHARD30Apr-18
MAGDKSANDANA5May-18
AGURICHIEGIMRICCHARD8May-18
MAGDKSANDANA25Jun-18
AGURICHIEGIMRICCHARD8Jun-18
MAGDKSANDANA100Jul-18
AGURICHIEGIMRICCHARD5Aug-18
MAGDKSANDANA7Sep-18
AGURICHIEGIMRICCHARD10Oct-18
MAGDKSANDANA23Oct-18
AGURICHIEGIMRICCHARD4Nov-18
MAGDKSANDANA12Nov-18
AGURICHIEGIMRICCHARD10Dec-18

 

In the page, I made a filter that lists down "Q1, Q2, Q3, Q4" so the user can select the quarter he wants to see. I added another filter in the page to select 2019 only as well so that the numbers will show 2019 only. 

 

I used the TOTALQTD formula as a measure to compute for the current QTD numbers the reader would like to see.

My next problem comes  in when I need to compute for PREVIOUS QTD and LAST YEAR'S SAME QUARTER.

I tried the following formulas from dutchdatadude.com , replacing the correct column headers in these formulas, but I get error or no result at all.

 

dutch.JPG

 

 

Below is a screen shot of the report I have for now (the long table shows the correct numbers I want to see as a guide)

qtd rep.JPG

 

Hope somebody can help me out and advise the correct formulas to use!

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Quarter Formulas

Hi @newgirl ,

 

To create measures as below.

 

TotalAmountforSameQuarterLastYear = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date].[Date],-1,YEAR))
TotalAmountforLastQuarter = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date].[Date],-1,QUARTER))

123.png

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
newgirl Frequent Visitor
Frequent Visitor

Re: Quarter Formulas

Hi @v-frfei-msft ! 

I tried to input your formulas in my file....I almost got it. 

I dont' know what I'm doing wrong for the "Previous Quarter" , though. Do you have any idea?

 

qtd.png

 

 

 

 

 

Community Support Team
Community Support Team

Re: Quarter Formulas

Hi @newgirl ,

 

To create a CALENDAR table should help. Please have a try. If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
newgirl Frequent Visitor
Frequent Visitor

Re: Quarter Formulas

Hi @v-frfei-msft !

 

Here is the link for the file - 

https://drive.google.com/file/d/1TW-7qdHcoKikw2TCCq4mjjGy87s7fQkb/view?usp=sharing

 

There's no confidential info inside since I just made up the raw data for sample purposes. Kindly refer to Page 2.

My expected result for "Q_PQ" (it means QTD_Previous Quarter) is 86 but instead, my file shows 212.

 

Thank you!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 36 members 1,063 guests
Please welcome our newest community members: