Creating measures based on different deadline dates

[PaisleyPrince]

Hi,

I have an unusual report to create where i need to log the amount of invoices posted per department where each department will have a different deadline date for posting. The output should be as per the table below. Can you please advise the best way to achieve this ?

thanks

Scott

Department Name         Deadline Date      Invoices Posted        Invoices Not Posted         Total

Department 1                              7th                           24                                       76             100

Department 2                              14th                         34                                       26               60

[v-veshwara-msft]

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.

[PaisleyPrince]

Hi,

Many thanks for your help. I am almost there but i have a problem when splitting the numbers by month and i have pasted the relevant detail below. Any advice would be appreciated.

[v-veshwara-msft]

Hi @PaisleyPrince ,

Thanks for sharing the additional details and the screenshot.

The issue appears to come from deriving the month inside the measure using the deadline date. When Month is placed on the visual, the month calculated from the deadline date overrides the month coming from the Date table, which leads to incorrect results when splitting the values by month.

To avoid this, the month should always come from the Date table. The department deadline should be used only as a cutoff day within the selected month, not to determine the month itself.

One approach is to take the year and month from the Date table and then construct the deadline date for each department within that month before comparing it with the invoice posting date.

A measure pattern would look like this:

Invoices Posted =
VAR SelectedYear =
    YEAR ( MIN ( 'Date'[Date] ) )
VAR SelectedMonth =
    MONTH ( MIN ( 'Date'[Date] ) )
VAR DeadlineDay =
    MAX ( Division_DM[Deadline Day] )
VAR DeadlineDate =
    DATE ( SelectedYear, SelectedMonth, DeadlineDay )
RETURN
CALCULATE (
    COUNTROWS ( Invoices ),
    Invoices[PostedDate] <= DeadlineDate
)

If this still does not behave as expected in your model, please share a sample PBIX file or sample data. That will help in understanding the setup better and providing more targeted 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.

[PaisleyPrince]

Hi, 

thanks for your response. Unfortunately it still provides an incorrect split when it comes to getting values by month. I will look to provide some sample data and upload a file shortly.

Regards

Scott

[v-veshwara-msft]

Hi @PaisleyPrince ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out. 

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.

[v-veshwara-msft]

Hi @PaisleyPrince ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out and 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.

[SavioFerraz]

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 ✔

[krishnakanth240]

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]

[Shubham_rai955]

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

Invoices Posted := VAR CutoffDate = DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), MAX ( DimDepartment[DeadlineDay] ) ) RETURN CALCULATE ( COUNTROWS ( FactInvoices ), FactInvoices[PostDate] <= CutoffDate ) Total Invoices := COUNTROWS ( FactInvoices ) Invoices Not Posted := [Total Invoices] - [Invoices Posted]

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

[danextian]

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.

[Ashish_Mathur]

Hi,

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

[jgeddes]

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.