Excel 365 Pro Plus with Power Pivot and Power Query.
Transform cumulative amounts to quarterly additions.
Starting with zero amount at beginning of each year.
https://www.mediafire.com/file/t7jntlyhpgearms/06_15_22.xlsx/file
https://www.mediafire.com/file/vq3dw5s2lb7zs0b/06_15_22.pdf/file
DAX difference between quarter excluding Q1
Hi forum!
I have a time series sliced by quarter but each quarter is cumulative. So Q1 is sales data for Q1, but Q2 is sales data for Q1+Q2, in Q3 it's sales data for Q1+Q2+Q3, and for Q4 it's sales data for Q1+Q2+Q3+Q4. This data is given to me like this and I cannot ask for it to be otherwise.
Thing is, I need the value for each quarter, not cumulative.
I've been racking my brain around in DAX for Power Pivot and I've managed to substract the quarter to get each value; thing is it does the same for Q1. It's substracting the value of Q4, cumulative, for Q one so the output for Q1 is rubbish.
I need to find a way of substracting the values for each quarter except when it's Q1.
Is there anyway to do this?
Below a simple example file of what I need.
https://1drv.ms/x/s!AqEBYAVXS3ejg6VA8J0p_yPhbcDrHw?e=GkJyyD
Thanks!
EDIT:
I'm hoping for a DAX measure that will return the Expected out in the sample file. I intend to use this measure in other measure later on.
I've updated the file linked above with a more complete version of the data model I'm using for a better reference.
2 answers
Sort by: Most helpful
-
Herbert Seidenberg 1,191 Reputation points
2022-06-16T02:36:33.177+00:00 -
Emi Zhang-MSFT 27,476 Reputation points Microsoft Vendor
2022-06-16T06:24:35.55+00:00 Hi @Shinka ,
Try this formula:=IF(RIGHT(A2,2)="Q1",B2,IF(RIGHT(A2,2)="Q2",B2-SUMPRODUCT(($A$2:$A$18=LEFT(A2,5)&1)*$B$2:$B$18),IF(RIGHT(A2,2)="Q3",B2-SUMPRODUCT(($A$2:$A$18=LEFT(A2,5)&2)*$B$2:$B$18),IF(RIGHT(A2,2)="Q4",B2-SUMPRODUCT(($A$2:$A$18=LEFT(A2,5)&3)*$B$2:$B$18),""))))
If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.