Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there, I'm trying to convert from data that is in YTD format for every quarter, to doing a subtraction to get to the true QTD value/ But this subtraction should not be subtracting Q4 of the previous year to Q1 of this year.
I have this formula:
AmountQTD = Sum([Amount])-CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date]))
How do I modify it to only subtract amounts that reside in the same calender year?
Solved! Go to Solution.
Hi @PuzzledSpark,
Let me check if my understanding is right. In table visual, you select Year_Quarter as row, Sum([Amount]) calculates the total amount of current quarter, the CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date])) display the previous Quarter amount. You want to calculate the difference total amount between Current Quarter and previous Quarter using the formula, right? For my sample table, For 2014 Q1(highlighted in yellow), the difference should be 4439, rather than 4439(2014 Q1)-4542((2013 Q4), isn't it?
If it is, you should create a calculated column to get quarter column, then create a measure to get the difference, you should use the formula as follows.
Quarter =
CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) )
Difference =
IF (
FIRSTNONBLANK ( dimPeriodDAX[Quarter], 1 ) = "Q1",
SUM ( [Amount] ),
SUM ( [Amount] )
- CALCULATE ( SUM ( [Amount] ), PREVIOUSQUARTER ( dimPeriodDAX[Date] ) )
)
In my sample table, I create a measure using the formula and get the following expected result.
quarter-difference = IF ( FIRSTNONBLANK ( 'Calendar'[Quarter], 1 ) = "Q1", SUM ( Sales[SALE] ), SUM ( Sales[SALE] ) - CALCULATE ( SUM ( Sales[SALE] ), PREVIOUSQUARTER ( 'Calendar'[Date] ) ) )
Please the three highlighted rows in my result.
Please let me know if you have any other issue.
Best Regards,
Angelia
Hi @PuzzledSpark,
Let me check if my understanding is right. In table visual, you select Year_Quarter as row, Sum([Amount]) calculates the total amount of current quarter, the CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date])) display the previous Quarter amount. You want to calculate the difference total amount between Current Quarter and previous Quarter using the formula, right? For my sample table, For 2014 Q1(highlighted in yellow), the difference should be 4439, rather than 4439(2014 Q1)-4542((2013 Q4), isn't it?
If it is, you should create a calculated column to get quarter column, then create a measure to get the difference, you should use the formula as follows.
Quarter =
CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) )
Difference =
IF (
FIRSTNONBLANK ( dimPeriodDAX[Quarter], 1 ) = "Q1",
SUM ( [Amount] ),
SUM ( [Amount] )
- CALCULATE ( SUM ( [Amount] ), PREVIOUSQUARTER ( dimPeriodDAX[Date] ) )
)
In my sample table, I create a measure using the formula and get the following expected result.
quarter-difference = IF ( FIRSTNONBLANK ( 'Calendar'[Quarter], 1 ) = "Q1", SUM ( Sales[SALE] ), SUM ( Sales[SALE] ) - CALCULATE ( SUM ( Sales[SALE] ), PREVIOUSQUARTER ( 'Calendar'[Date] ) ) )
Please the three highlighted rows in my result.
Please let me know if you have any other issue.
Best Regards,
Angelia
Thanks I tried this out but found the sub-totals are not correct. Am going to redirect this to my other post https://community.powerbi.com/t5/Desktop/Subtotal-not-correct/m-p/331061#M147994
Last column in screenshot below is what I got wiith your method. Tempted to mark it as the answer as technically it achieved the original ask but want to wait a little longer first.
Hi @PuzzledSpark,
The latest post is related the original thread above you post? It's confusing. Please create a new thread if you have new problem. One thread includes one issue here.
Best Regards,
Angelia
You don’t provide enough info to be 100% sure. But try this:
CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date]), MAX ( dimPeriodDAX[Year] ) )
this assumes your calendar table has a ‘Year’ column. I have a similar scenario but use a slicer for year and do this QTD calculations:
TOTALQTD ( SUM ( Table[Amount] ) , PREVIOUSQUARTER( Calendar[Date] ), VALUES ( Calendar[Year] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |