Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Do you have a date table?
Proud to be a Super User!
Paul on Linkedin.
Hello @hemann ,
@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.
Hi,
Share the download link of the PBI file clearly showing the problem there.
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 |
Sorry but just cannot understand what you want. It's been a while since i had reuested for the data.
ProductQuarterActualVolume =
CALCULATE (
SUMX ( ALLSELECTED ( Community_Table1[DateEnd] ), [Total Actual Volume] ),
DATESINPERIOD (
Community_Table1[DateEnd],
LASTDATE ( Community_Table1[DateEnd] ),
-3,
MONTH
)
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
68 | |
60 | |
51 | |
36 | |
35 |
User | Count |
---|---|
84 | |
71 | |
56 | |
45 | |
43 |