Sum monthly calculations into quarter

[hemann]

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. 

[PaulDBrown]

Do you have a date table?

[Mahesh0016]

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]

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

[hemann]

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. 

[Ashish_Mathur]

Hi,

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

[hemann]

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

[Ashish_Mathur]

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

[Mahesh0016]

@hemann 

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