Forum Discussion
Calculation Adjustment based on Period
Hi Sachintha ,
In your updated requirement, I noticed a discrepancy between the outcomes for Campaign #6 in Table 1 and Table 2, particularly regarding Steve and Summer. Could you please confirm whether the numbers in Table 2 are correct, rather than those in the first summary table, which I assume reflects Campaign #6?
With the assumption above, your required output can be produced by the following dax formula:
Test count (adjusted) 2 =
SUMX(
SUMMARIZE(
'Employee tests',
'Employee tests'[Campaign #], -- Summarize by Campaign #
"AdjustedTestCount",
VAR Testcount = DISTINCTCOUNT('Employee tests'[TestId])
VAR IsCurrentPeriod = [IsCurrentPeriod]
VAR PreviousCampaign =
CALCULATE(
MAX('Employee tests'[Campaign #]),
FILTER(
'Employee tests',
'Employee tests'[Campaign #] < MAX('Employee tests'[Campaign #]) -- Fetch the previous campaign #
)
)
VAR PreviousTestCount =
CALCULATE(
DISTINCTCOUNT('Employee tests'[TestId]),
'Employee tests'[Campaign #] = PreviousCampaign -- Get test count of the previous campaign
)
RETURN
IF(
PreviousTestCount >= 1 && IsCurrentPeriod = BLANK(), -- Check if the previous campaign's Testcount is >= 1
PreviousTestCount - 1 +Testcount, -- Subtract 1 from the current Testcount
Testcount -- Otherwise, return the current Testcount without adjustment
)
),
[AdjustedTestCount]
)
The output is as shown below:
I have attached an example pbix file.
DataNinja777 the numbers are actually correct. It should be this:
I think there's perhaps a little misunderstanding on how the count should work. Let me take Steve and Summer and try to explain.
Steve:
He had fails during each campaign as follows.
| Campaign # | Fails during Campaign |
| 1 | 1 |
| 2 | 0 |
| 3 | 3 |
| 4 | 1 |
| 5 | 0 |
| 6 | 0 |
We subtract a 'fail' after a campaign is concluded. In other words, at the beginning of the next campaign. I think this is where the discrepancy may be coming from. So, because of this, we don't subtract a fail during the campaign. In other words, there should not be an adjustment for the 1st campaign during that campaign period.
With that in mind:
| Campaign # | Fails during Campaign | Adjusted Fails |
| 1 | 1 | No adjustment made |
| 2 | 0 | (1 - 1) + 0 = 0 Here, we subtract 1 from the previous campaign total, then add to the current campaign total of zero, so we get zero as the adjusted total. |
| 3 | 3 | (0) + 3 = 3 Since the adusted total now is zero, we don't further subtract even though we're at the next campaign now. Then, we add the 3 accrued during this campaing for a total of 3 adjusted fails. |
| 4 | 1 | (3 - 1) + 1 = 3 We subtract 1 from the adjusted total of 3 from the last campaign, then add the 1 accrued this campaign, for a total of 3 again. |
| 5 | 0 | (3 - 1) + 0 = 2 Previous campaign adjusted total was 3, so we subtract 1 then add zero to it, so we end up with 2. |
| 6 | 0 | (2 - 1) + 0 = 1 Previous campaign adjusted total was 2, so we subtract 1 then add zero to it, so we end up with 1. |
Following along the same logic for Summer:
| Summer | 1 | 1 | No adjustment |
| Summer | 2 | 0 | (1 -1) + 0 = 0 Previous campaign total 1 goes down to zero, plus zero from this campaign. |
| Summer | 3 | 0 | (0) + 0 Previous total was zero, so we do not subtract. And no fails this campaign. |
| Summer | 4 | 0 | (0) + 0 Same as before |
| Summer | 5 | 3 | 0 + 3 = 3 Previous was zero, so we don't subtract. But now we have 3 during this campaign, so total is 3. |
| Summer | 6 | 1 | (3 - 1) + 1 = 3 Previous total of 3 goes down to two, and then add the 1 accrued this campaign, for a new total of 3. |
Hope it's clear now.