Creating measures based on different deadline dates

Hi PaisleyPrince ,

This is an interesting scenario, and it’s actually a very good use case for a small helper (dimension) table combined with DAX measures.

Recommended approach (best practice)

Instead of trying to hard-code different deadlines inside measures, the clean and scalable solution is to model the deadlines as data.

✅ Step 1 – Create a Department Deadline table

Create a small table like this (manually or from your source):

Department DeadlineDay
Department 1 7
Department 2 14

This table defines the posting deadline day of the month per department.

Relate this table to your Invoices table by Department.

Step 2 – Base measures

Assume your Invoices table has:

Department

InvoiceDate

PostedFlag (or similar, TRUE/FALSE)

Total Invoices
Total Invoices =
COUNTROWS ( Invoices )

Step 3 – Invoices Posted (before or on deadline)
Invoices Posted :=
VAR DeadlineDay =
MAX ( DepartmentDeadline[DeadlineDay] )
VAR MonthEndDate =
DATE (
YEAR ( MAX ( Invoices[InvoiceDate] ) ),
MONTH ( MAX ( Invoices[InvoiceDate] ) ),
DeadlineDay
)
RETURN
CALCULATE (
COUNTROWS ( Invoices ),
Invoices[InvoiceDate] <= MonthEndDate
)

This dynamically:

Reads the deadline for the department

Builds the correct cutoff date

Counts invoices posted up to that date

Step 4 – Invoices Not Posted
Invoices Not Posted :=
[Total Invoices] - [Invoices Posted]

Result

Your matrix visual will naturally produce:

Department Deadline Invoices Posted Invoices Not Posted Total
Dept 1 7th 24 76 100
Dept 2 14th 34 26 60

No special visuals or tricks needed — the model does the work.

Why this approach works well

✔ Scales to any number of departments
✔ Easy to maintain (deadlines are data, not code)
✔ Works with slicers (month, year, department)
✔ Much cleaner than nested IF logic in measures

Conceptual illustration
Invoices
|
| (Department)
▼
DepartmentDeadline
├── Department
└── DeadlineDay

Measure logic:
InvoiceDate <= EndOfMonth + DeadlineDay

Hope this helps clarify the best way to approach it 👍
If this answered your question, please consider giving it a kudos
and mark it as the Accepted Answer ✔