Forum Discussion
Rolling subtraction by quarter
I have the total number (108) and i want to subtract it with the value i have every quarter. (I can have the total number as a Column or as measure.) My problem is that I want to use the "Count by quarter", which is giving me the total amount of items that need to be subtracted. The way the report is builded, i have the same number for every record. What i want to have at the end is
Total = 108
Q4 2022 = 7
New Total = 108-7 = 101
Q1 2023 = 17
New total = 101-17= 84
etc
Any idea how can i do it with DAX or some other method?
Thank you in advance
Hi Anonymous ,
We need a column like [Index] or [YearQuarter] to sort your [QT] column. Here I suggest you to create a DimDate table by dax.
DimDate = ADDCOLUMNS ( CALENDAR ( DATE ( 2022, 01, 01 ), DATE ( 2023, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Quarter", QUARTER ( [Date] ), "QT", "Q" & "" & FORMAT ( [Date], "Q YYYY" ), "QY", YEAR ( [Date] ) * 100 + QUARTER ( [Date] ) )Relationship:
Then you can try this code to create a calcualted column.
Rolling subtraction by quarter = VAR _STEP1 = SUMMARIZE ( ALL ( 'Table' ), 'Table'[Burndown], 'Table'[Count by quarter], 'Table'[QT], "QY", CALCULATE ( MAX ( DimDate[QY] ) ) ) VAR _STEP2 = ADDCOLUMNS ( _STEP1, "Rolling subtraction by quarter", 108 - SUMX ( FILTER ( _STEP1, [QY] <= EARLIER ( [QY] ) ), [Count by quarter] ) ) RETURN SUMX ( FILTER ( _STEP2, [QT] = EARLIER ( 'Table'[QT] ) ), [Rolling subtraction by quarter] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
We need a column like [Index] or [YearQuarter] to sort your [QT] column. Here I suggest you to create a DimDate table by dax.
DimDate = ADDCOLUMNS ( CALENDAR ( DATE ( 2022, 01, 01 ), DATE ( 2023, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Quarter", QUARTER ( [Date] ), "QT", "Q" & "" & FORMAT ( [Date], "Q YYYY" ), "QY", YEAR ( [Date] ) * 100 + QUARTER ( [Date] ) )Relationship:
Then you can try this code to create a calcualted column.
Rolling subtraction by quarter = VAR _STEP1 = SUMMARIZE ( ALL ( 'Table' ), 'Table'[Burndown], 'Table'[Count by quarter], 'Table'[QT], "QY", CALCULATE ( MAX ( DimDate[QY] ) ) ) VAR _STEP2 = ADDCOLUMNS ( _STEP1, "Rolling subtraction by quarter", 108 - SUMX ( FILTER ( _STEP1, [QY] <= EARLIER ( [QY] ) ), [Count by quarter] ) ) RETURN SUMX ( FILTER ( _STEP2, [QT] = EARLIER ( 'Table'[QT] ) ), [Rolling subtraction by quarter] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.