Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate sum product for previous 4 quarters individually

I'm trying to create 4 DAX measures to calculate sum product of previous 4 quarters based on date selection from slicer. I'm using following DAX function to achieve that:

 

avg age qtr-1 = VAR _d1 = MAX(Sheet1[Date])
RETURN CALCULATE(SUMX(Sheet1, Sheet1[Balance] * Sheet1[Acc Age])/SUM(Sheet1[Balance]),DATESINPERIOD(Sheet1[Date],_d1,-3,MONTH))
avg age qtr-2 = VAR _d1 = MAX(Sheet1[Date])
RETURN CALCULATE(SUMX(Sheet1, Sheet1[Balance] * Sheet1[Acc Age])/SUM(Sheet1[Balance]),DATESINPERIOD(Sheet1[Date],_d1,-6,MONTH))
avg age qtr-3 = VAR _d1 = MAX(Sheet1[Date])
RETURN CALCULATE(SUMX(Sheet1, Sheet1[Balance] * Sheet1[Acc Age])/SUM(Sheet1[Balance]),DATESINPERIOD(Sheet1[Date],_d1,-9,MONTH))
avg age qtr-4 = VAR _d1 = MAX(Sheet1[Date])
RETURN CALCULATE(SUMX(Sheet1, Sheet1[Balance] * Sheet1[Acc Age])/SUM(Sheet1[Balance]),DATESINPERIOD(Sheet1[Date],_d1,-12,MONTH))

 

 

For some reason the values are not correct in the final output. 

For eg, for ID : 1, if I select Dec 31,2020 the correct values are:

 

Sept 30,2020: 48.49

June 30,2020: 55.92

Mar 31,2020: NA

Sept 30, 2020: 57.51

 

Please suggest any chages in formula or approach.

Thanks!

PBIX file is attached here for reference: https://drive.google.com/file/d/1eV-2kJlpXSMFRne4Pab9Pcc8L_-g6KGO/view?usp=sharing

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@amitchandak I tried your method but it gave me the same avg age for all the IDs. I did some more research and found that I have to release all filters except on ID in order to get correct numbers. I used ALLEXCEPT function to do that. Here's what my final formula looks like:

 

Avg age qtr-1 = CALCULATE(SUMX(Sheet1, Sheet1[Balance] * Sheet1[Acc Age])/SUM(Sheet1[Balance]),ALLEXCEPT(Sheet1,Sheet1[ID]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-3,MONTH))))

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , have you tried Datesqtd with date table ?

example

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak I tried your method but it gave me the same avg age for all the IDs. I did some more research and found that I have to release all filters except on ID in order to get correct numbers. I used ALLEXCEPT function to do that. Here's what my final formula looks like:

 

Avg age qtr-1 = CALCULATE(SUMX(Sheet1, Sheet1[Balance] * Sheet1[Acc Age])/SUM(Sheet1[Balance]),ALLEXCEPT(Sheet1,Sheet1[ID]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-3,MONTH))))

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.