How to write this expression in DAX?

Question

Hello&nbsp;Below is the scenario.&nbsp;Product ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VisitDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VisitStatus&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dollar $&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Expected&nbsp;output5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2/4/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Canceled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $10005&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3/2/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Canceled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3/15/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Canceled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $0&nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3/25/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Canceled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1000&nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4/8/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Check out&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$1000&nbsp;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?&nbsp;Thanks in advance.

teigegao · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,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

Forum Discussion

How to write this expression in DAX?

1 Reply

Related content

Warehouse write activity in a Pipeline

Help!!! Expression Error

Regular expression

Help writing a DAX expression to count rows by month and stage over a financial year

write DAX