Invoice Due Date before today show in graph on today

[Norbertus]

Hi,

I have a table with invoices wiht a due date. This date can be in the past if it is not payed.
Can i make in a measure (not a calculated column) that this outstanding amount is shown for today in a graph.
Invoices that have a due date in the future must shown on the future

Of course i can make a new calculated column to check the due date and give 'today' if it is in the past. 
But is this also posible in a measure?

with kind regards

[V-lianl-msft · Accepted solution]

Hi @Norbertus ,

Please try to create measure like this:

measure = CALCULATE(
    SUMX(
        FILTER('Table','Table'[End Date]<=MAX('Table 2'[Date]) && 'Table'[Status]="Outstanding"),
        ('Table'[Amount])),
    CROSSFILTER('Table'[Start Date],'Table 2'[Date],None))

Here is my sample .pbix

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Norbertus , You can have a measure like

calculate([measure], filter(Table, Table[Due Date] <=today()))

[Norbertus]

unfortunately,

this will not place the the invoices in the past on today in the graph 

VAR today= NOW()

RETURN

CALCULATE(
    SUM(Debtors[amount]),
    Debtors[Status] = "Outstanding",
    FILTER(
        Debtors,
        Debtors[Invoice Due Date] <= today)
)

With a calculated column i can make a new data column and with a Userrelationship i can make a relation with the calendertable. But is this possible in a measure?

[amitchandak]

@Norbertus , use relationship and formula you shared is possible as a measure. Are you getting any error

[Norbertus]

That is not a problem..

for now i make a calculated column with a "if scenario" to calculate the new paymemnt day (today) if the invoice is overdue. With a UserRelationship i can make the graph over time.