Forum Discussion
Calculation Adjustment based on Period
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,
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.
