cancel
Showing results for
Did you mean:

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

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.

8 REPLIES 8
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!

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

Helper I

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.

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

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.

 DateEnd Product Actual Volume Friday, 30 September 2022 A 278 Friday, 30 September 2022 B 177 Friday, 30 September 2022 C 4 Friday, 30 September 2022 D 1 Friday, 30 September 2022 E 2 Monday, 31 October 2022 A 93 Monday, 31 October 2022 B 104 Sunday, 31 July 2022 A 420 Sunday, 31 July 2022 B 338 Sunday, 31 July 2022 C 5 Sunday, 31 July 2022 D 2 Wednesday, 31 August 2022 A 348 Wednesday, 31 August 2022 B 309 Wednesday, 31 August 2022 C 8 Wednesday, 31 August 2022 D 2
 DateEnd Estimate 30-Nov-21 0 31-Dec-21 0 31-Jan-22 0 28-Feb-22 0 31-Mar-22 0 30-Apr-22 0 31-May-22 120 30-Jun-22 132 31-Jul-22 142 31-Aug-22 158 30-Sep-22 239 31-Oct-22 507
 DateEnd End of Quarter 31-Oct-22 31-Dec-22 30-Sep-22 30-Sep-22 31-Aug-22 30-Sep-22 31-Jul-22 30-Sep-22 30-Jun-22 30-Jun-22 31-May-22 30-Jun-22 30-Apr-22 30-Jun-22 31-Mar-22 31-Mar-22 28-Feb-22 31-Mar-22 31-Jan-22 31-Mar-22
Super User

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
Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors