DAX formula not adhering to time selection

[Erwin]

Hi all,

I have an interesting question about a DAX formula that does not respond correctly when time is restricted. Please review attached picture of my report.

What I want the measure "Amount of Last Invoice Sent" to do is to show the amount of the latest invoice on the Total line. So that when the user expands all projects he will see the amount of the invoice that was sent last for each project.

This works great when time is not restricted in any way. As you can see in the top table the value of the invoice with the most current baseline date (last row) is also shown as Total. However, when I restrict time to somewhere in the past (e.g. 31-7-2017) the measure doesn't give me a value anymore (see lower table).

Can anybody tell me why this is and how to get around this?

Many thanks in advance.

Erwin

[MFelix · Accepted solution]

Hi @Erwin,

Try to change your measure to:

AMOUNT last invoice =
VAR Last_Date =
    MAX ( TTCFactTable[Baseline Date] )
RETURN
    CALCULATE (
        [AMOUNT of Invoices Sent];
        TTCFactTable[Baseline Date] = Last_Date;
        ALLSELECTED ( TTCFactTable[Baseline Date] )
    )

Regards,

MFelix