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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
hemann
Helper I
Helper I

Sum monthly calculations into quarter

I have performed two calculations at a month level. I then want to add the two monthly calculations for the latest quarter. When I sum for latest quarter, the measure reverts to performing the earlier calculations at a quarterly level and then roles it up.

 

Taking October 2022 as example, Measure 9 and 12 are the calculations at a monthly level, i then want to add them together for the 3 months. The answer I get is 1,698.27 but it should be 1,649 (93+478+278+287+348+164=1,649). Help is much appreciated, given me a headache this one. 

hemann_1-1670908103047.png

 

 

 

8 REPLIES 8
PaulDBrown
Community Champion
Community Champion

Do you have a date table?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Mahesh0016
Super User
Super User

Hello @hemann ,

Three Month sales = CALCULATE(SUMX(Table7,[Total List Price]+[Total Sales Price]),DATESINPERIOD(Table7[Saledate],MAX(Table7[Saledate]),-3,MONTH))
 
i hope it is helpful try it....
amitchandak
Super User
Super User

@hemann , Not very clear if you want rolling 3 or quarter

 

for Qtr

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

 

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

I don't really mind if it's quater to date or rolling quarter. The issue is when rolling up the quarter (adding the individual months of measure 9 and 12 which have specific allocations performed at a monthly level). When rolling up it performs those specific monthly allocations at a quarterly level, which I don't want. 

Take example below I performed datesqtd and endofquarter. For September 2022 quarter it gives 1,640.24 but should be 1,652 based on measure 9 & 12; 278+287+348+164+420+155 = 1,652 (based on monthly calculations of measure 9 & 12). October 2022 is fine at 571 (93 + 478) because there is only one month so far.

I need the measure to simply add the 3 months (for measure 9 & 12) and preserve the previous monthly allocations performed on measure 9 & 12. Hope this is somewhat clear. 

hemann_0-1671058783667.png

 

Hi,

Share the download link of the PBI file clearly showing the problem there.


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

I have copied sample data below, there are x3 tables. Sorry could not share download link of pbix. Below are measures I have created, issue it will not sum for quarter. 

hemann_0-1672789811367.png

Result I am getting is below. This is calculating correctly at monthly level, but not rolling up for quarter. Below is desired result, October 2022 Product A = 465 (239+143+82). When rolling quarter the monthly calculation needs to be preserved. 

hemann_1-1672789876021.png

hemann_2-1672790061108.png

 

 

DateEndProductActual Volume
Friday, 30 September 2022A278
Friday, 30 September 2022B177
Friday, 30 September 2022C4
Friday, 30 September 2022D1
Friday, 30 September 2022E2
Monday, 31 October 2022A93
Monday, 31 October 2022B104
Sunday, 31 July 2022A420
Sunday, 31 July 2022B338
Sunday, 31 July 2022C5
Sunday, 31 July 2022D2
Wednesday, 31 August 2022A348
Wednesday, 31 August 2022B309
Wednesday, 31 August 2022C8
Wednesday, 31 August 2022D2
DateEndEstimate
30-Nov-210
31-Dec-210
31-Jan-220
28-Feb-220
31-Mar-220
30-Apr-220
31-May-22120
30-Jun-22132
31-Jul-22142
31-Aug-22158
30-Sep-22239
31-Oct-22507
DateEndEnd of Quarter
31-Oct-2231-Dec-22
30-Sep-2230-Sep-22
31-Aug-2230-Sep-22
31-Jul-2230-Sep-22
30-Jun-2230-Jun-22
31-May-2230-Jun-22
30-Apr-2230-Jun-22
31-Mar-2231-Mar-22
28-Feb-2231-Mar-22
31-Jan-2231-Mar-22

Sorry but just cannot understand what you want.  It's been a while since i had reuested for the data.


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

@hemann 

Mahesh0016_1-1672821153110.png

ProductQuarterActualVolume =
CALCULATE (
SUMX ( ALLSELECTED ( Community_Table1[DateEnd] ), [Total Actual Volume] ),
DATESINPERIOD (
Community_Table1[DateEnd],
LASTDATE ( Community_Table1[DateEnd] ),
-3,
MONTH
)
)

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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