How to write this expression in DAX?

[Syruswan]

Hello

Below is the scenario.

Product ID              VisitDate              VisitStatus              Dollar $           Expected output

5                            2/4/2018               Canceled                $1000              $1000

5                            3/2/2018               Canceled                $1000              $0

5                            3/15/2018             Canceled                $1000              $0 

5                            3/25/2018             Canceled                $1000              $1000 

5                            4/8/2018               Check out               $1000             $1000

My expected output is in red. If there are several visits canceled in the same month, I only want to keep one dollar value for the latest visit date. In my example, there are 3 visits canceld in March and I only want the column show the dollar value on the latest date in that specific month. Is it something doable?

Thanks in advance.

[TeigeGao · Accepted solution]

Hi @Syruswan ,

In your scenario, we can create a calculated column with the following DAx query:

Expected output =
IF (
    CALCULATE (
        MAX ( Table1[VisitDate] ),
        FILTER ( Table1, Table1[Months] = EARLIER ( Table1[Months] ) )
    ) = Table1[VisitDate],
    LOOKUPVALUE (
        Table1[Dollar$],
        Table1[VisitDate], CALCULATE (
            MAX ( Table1[VisitDate] ),
            FILTER ( Table1, Table1[Months] = EARLIER ( Table1[Months] ) )
        )
    ),
    0
)

The result will like below:

Best Regards,

Teige