Creating measures based on different deadline dates

If you are able to shape the data model you can do something like this.

Department dimension table

Invoice Fact table

Relationship

Invoices Not Posted measure

Invoices Not Posted = 
COUNTX(
    FILTER(
        factTable,
        DAY(factTable[Posted Date]) >= RELATED(dimensionTable[Deadline Date])
    ),
    [Posted Date]
)+0

Invoices Posted measure

Invoices Posted = 
COUNTX(
    FILTER(
        factTable,
        DAY(factTable[Posted Date]) < RELATED(dimensionTable[Deadline Date])
    ),
    [Posted Date]
)+0

Total Invoices measure

Total Invoices = 
[Invoices Not Posted] + [Invoices Posted]

Final output

The Department column and the Deadline Date column are from the dimension table. Also, the 'Show items with no data' option is enabled on the Department column.

Hope this points you in the right direction. 

Hi,

Share som e data to work with.  SHare it in a format that can be pasted in an MS Excel file.

Hi PaisleyPrince 

Please provide a workable sample data (not an image), which result  will match your sample output and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. Please do not use a sample data with a different output and make us figure out why the difference.

Use a per‑department deadline column in a small dimension, relate it to your fact, then write 3 measures.

Assume:

• DimDepartment[Department]

• DimDepartment[DeadlineDay] (number like 7, 14)

• FactInvoices has [Department], [PostDate].

Put DimDepartment[Department] on rows and these three measures in values; each department will use its own cutoff day from the dimension.

Hi PaisleyPrince 

1. Department Deadline Table (Dimension)

Create a separate table (or import one):

Department DeadlineDay

Department 1 7
Department 2 14

➡ DeadlineDay = day of month

Relate:

Invoices[Department] → DepartmentDeadline[Department]

2. Invoice Fact Table (Minimum Fields Needed)
InvoiceID Department PostingDate

Key DAX Measures
Total Invoices
a)Total Invoices :=
COUNT ( Invoices[InvoiceID] )

Invoices Posted (On or Before Deadline)
b)Invoices Posted =
VAR Deadline =
MAX ( DepartmentDeadline[DeadlineDay] )
VAR CurrentMonth =
MAX ( MONTH ( Invoices[PostingDate] ) )
VAR CurrentYear =
MAX ( YEAR ( Invoices[PostingDate] ) )

VAR DeadlineDate =
DATE ( CurrentYear, CurrentMonth, Deadline )

RETURN
CALCULATE (
COUNT ( Invoices[InvoiceID] ),
Invoices[PostingDate] <= DeadlineDate
)

Invoices Not Posted (After Deadline)
c)Invoices Not Posted :=
[Total Invoices] - [Invoices Posted]

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 ✔

Hi PaisleyPrince ,

Thanks for reaching out to Microsoft Fabric Community.

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance. Also could you please share sample data or PBIX file for better guidance. You could upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link here.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.