Forum Discussion

PaisleyPrince's avatar
PaisleyPrince
Advocate II
8 months ago

Creating measures based on different deadline dates

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...
Need Help
Shubham_rai955's avatar
Shubham_rai955
Super User
7 months ago

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.