Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
)
)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.