Power Bi, Dax Syntax to subtract TODAY date with criteria from a column with repeated dates

0
votes

The problem is, I have table with products, their "adresses" and the expiration date, just like below:

Table Image

I need to show the "days to expire" in a matrix-visual so I tried to make a DAX formula as it goes trying to get the "days to expire" from each product and it's expiration date uniquely, but the result was way far from what i wanted.

DaysToExpire = CONVERT(SUM(Venda_Abast_Venc[DTVALIDADE]) - TODAY(), INTEGER)

It resulted in the sum of all the expirations dates by product and subtracted by the Today() date, the number was really higher than the real value when I needed it uniquely.

2
datepowerbidaxsubtraction

2 Answers

0
votes

This is fairly straight forward, add a new column:

DaysToExpire = DateDiff(Today(), Venda_Abast_Venc[DTVALIDADE], DAY)
0
votes

The problem is: what happens if your matrix selection brings more than one product with different expiration dates? Assuming the safest option, I'll use the minimum expiration date.

DaysToExpire =
INT( MIN( Venda_Abast_Venc[DTVALIDADE] ) - TODAY() )

this way dates are not summed up and just one date is selected for the computation