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
jgeddes's avatar
jgeddes
Super User
8 months ago

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.