Calculate on reconstructed date not working

[kylee_anne]

Hi Everyone,

So this is my code:

Delivered Last 24hrs =

var toddy = MAX('Report Date'[Report Date])

var yesterday = day(toddy)-1

return

CALCULATE(sum('Daily Cement Delivered'[Value])+0,'Daily Cement Usage'[Date]= date(year(toddy),month(toddy),yesterday))

Report Date is 11/8/25 so my reconstructed calculate condition is 10/8/25 which should give me as answer 23 tonnes.  But it is actually not filtering at all and summing everything.  

Any ideas what I'm missing?  Is it a time thing?

[rohit1991]

Hi @kylee_anne 

The issue is happening because your reconstructed date is being compared to a datetime column, and the time portion in the datetime is preventing a proper match. To fix this, you need to compare only the date part without the time. You can do this by using INT or DATEVALUE to strip out the time from both sides of the comparison. Also, wrap your filter logic inside a FILTER function so it applies row-by-row to the table. Using ALL inside the FILTER ensures that no existing filters block your comparison. This way, the calculation will correctly match the intended date and return only the relevant rows instead of summing everything.

[wardy912 · Accepted solution]

Hi @kylee_anne 

 You need to wrap the second part of the CALCULATE statement in a FILTER function to accurately apply row by row filtering. I've also added INT to ensure the date you are using is only using the date if the type is actually datetime. Finally, i've added +0 to deal with any blanks.

Delivered Last 24hrs =
VAR toddy = MAX('Report Date'[Report Date])
VAR yesterday = toddy - 1
RETURN
CALCULATE(
    SUM('Daily Cement Delivered'[Value]) + 0,
    FILTER(
        'Daily Cement Usage',
        INT('Daily Cement Usage'[Date]) = INT(yesterday)
    )
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!