Calculation Adjustment based on Period

Sachintha · ‎09-04-2024

My company has a set of ongoing Phishing Test sent out to employees in order to strengthen our cyber security. During each time periods, the employees will get one or more simulated phishing emails, and they are expected to not 'fail' those. A failure being an instance of an employee clicking a link, download an attachment, etc. on those emails. I get these test results from an API provided by the test provider. The data is structured like this:

1. List of 'campaigns' with start and end dates (Campaigns.csv)

Campaign,StartDate,EndDate
1,2022-01-01,2022-06-30
2,2022-07-01,2022-12-31
3,2023-01-01,2023-06-30
4,2023-07-01,2023-12-31
5,2024-01-01,2024-06-30
6,2024-07-01,2024-12-31

2. List of instances where employees failed the tests (CampaignResults.csv)

TestId,Date,Employee
1,2022-01-17,Clark
2,2022-02-23,Howard
3,2022-02-27,Howard
4,2022-03-17,Jane 
5,2022-05-02,Summer
6,2022-06-20,Steve
7,2022-07-12,Jane 
8,2022-07-29,Clark
9,2022-08-01,Clark
10,2023-01-19,Howard
11,2023-02-13,Steve
12,2023-03-27,Jane 
13,2023-04-15,Clark
14,2023-04-29,Howard
15,2023-05-08,Steve
16,2023-06-14,Steve
17,2023-07-27,Steve
18,2023-09-19,Clark
19,2023-12-02,Jane 
20,2023-12-28,Howard
21,2024-01-04,Summer
22,2024-02-23,Summer
23,2024-03-29,Jane 
24,2024-04-17,Summer
25,2024-07-22,Jane 
26,2024-11-22,Mia
27,2024-12-09,Eric
28,2024-09-03,Summer

The campaign periods are set to even six months for the convenience of this questions, but in reality they are arbitrary dates. Since the CampaignResults file doesn't have hte campaign ID, I created a calculated column in the CampaignResults table in DAX like so:

Campaign = CALCULATE(
        VALUES(Campaigns[Campaign]),
        FILTER(
            Campaigns,
            [Date] >= Campaigns[StartDate] && [Date] <= Campaigns[EndDate]
        )
)

My requirement is to make charts to analyze this data, for example, total number of fails per employee. Just adding up the instances is easy with a Measure like this:

Fails = CALCULATE(DISTINCTCOUNT(CampaignResults[TestId]))

And I get a table like this:

However, my actual requiredment is a little complicated. Our company has a policy where after every campaign period, one failure is dropped from each employee per campaing periood (unless you don't have any failures, in which case your total is not going to go down to -1). This process is repeated for each campaign.

To illustrate the idea better, let's take an employee from the data set (Summer). She has the following record:

As you can see, she had 1 failure during campaign 1, then none until campaign 5 where she had 3 failures, and another one in campaign 6.

I need to calculated an 'Adjusted Fails' based on following rules.

Adjusted Fails =

Must count all fails occurred during the current period.
- In her case, 1.
Drop 1 failure per period until you hit zero from previous campaigns.

So, Summer's total should be 3. We arrive at this by following logic:

Subtract 1 at the end of campaign 1. New total = 0.
For campaigns 2 through 4, she doesn't have any more fails, and her total is zero, so it stays at zero.
During campaign 5 she accrued 3 fails, and at the end of it, we subtract 1, so total adjusted fails is now 2.
Then during last (current) campaign, she has another fail, and since we don't subtract any from the current campaign, we add it to her previous total of 2, so we get 3.

Following along this logic, following is the desired table for each employee:

What's the best way to calculate this measure? I thought about summing up the totals, and subtractring by the number of years, but this doesn't work for instances such as Summer's where there are numerous fails in recent campaign periods.

Thanks in advance!

EDIT:

I made a mistake in my original post, the Adjusted Fails totals shown are wrong. They should look like this:

The way you'd arrive at those calculations is described below:

DataNinja777 · ‎09-06-2024

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.

Sachintha · ‎09-06-2024

@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.

DataNinja777 · ‎09-04-2024

Hi @Sachintha ,

You can achieve your required output by writing a measure as shown below:

Test count (adjusted) = 
SUMX(
    SUMMARIZE(
        'Employee tests',
        'Employee tests'[Campaign #],  -- Summarize by Campaign #
        "AdjustedTestCount", 
        VAR Testcount = DISTINCTCOUNT('Employee tests'[TestId])
        VAR IsCurrentPeriod = [IsCurrentPeriod]
        RETURN
        IF(
            Testcount >= 1 && IsCurrentPeriod = BLANK(),
            Testcount - 1,  -- Adjust the count when not in the current period
            Testcount  -- Otherwise return the actual count
        )
    ),
    [AdjustedTestCount]
)

Where the IsCurrentPeriod measure is written as follows:

IsCurrentPeriod = 
    VAR CurrentStartDate = 
        CALCULATE(
            MAX(Campaigns[StartDate]), 
            FILTER(Campaigns, Campaigns[Current period] = "Current period")
        )
    VAR CurrentEndDate = 
        CALCULATE(
            MAX(Campaigns[EndDate]), 
            FILTER(Campaigns, Campaigns[Current period] = "Current period")
        )
    VAR TestDate = 
        SELECTEDVALUE('Employee tests'[Date])

    RETURN
        IF(
            NOT(ISBLANK(CurrentStartDate)) && 
            NOT(ISBLANK(CurrentEndDate)) && 
            TestDate >= CurrentStartDate && TestDate <= CurrentEndDate,
            "Current period",
            BLANK()
        )

The data model is structured as follows: the Calendar and Campaigns tables are disconnected, while the Employee tests table is related to the Calendar table by the date key.

The resulting output is shown below:

I have attached an example pbix file.

Best regards,

Sachintha · ‎09-05-2024

@DataNinja777 thank you for the response!

This almsot works, but I believe I made a mistake in the last table screenshot I posted. The adjusted fails for each employee should be as follows:

How you'd arrive at those calculations is as follows. Basically, subtract a fail at the end of each campaign (unless you go into negative), then add the current campaign total. Repeat for each year (again, never let it go into negative). No deductions for the current year.