Something odd with my "to date formula" - a bug?

[LaurenceSD]

Hi,

I've encountered something very strange today, with a formula that previously worked fine, but now for some reason it's thrown a wobbly.

Bookings Budget UK = sum(UKBudget[BkgsPerDay])

Bookings Budget UK To Date = CALCULATE([Bookings Budget UK],FILTER(Dates,Dates[Date]<=today()))

It's the 2nd of these that I'm having an issue with and before today, the formula was producing the expected result, but now it's gone haywire

Both measures as you can see from the same table, & I've a number of one to many relationships coming off them, such as data table, office table, etc

I've taken a screenshot of what it's doing. As you can see in the bottom left, if i don't add office to the table, the measure works as expected. But when I add in office it goes balnk - I could understand if I'd not linked up the tables correctly, but the standard bookings still pulls through fine, it's only the to date one. Does anyone have any ideas? Thanks

[daxer-almighty]

What you're doing here:

FILTER(Dates,Dates[Date]<=today()))

is not filtering all dates so that all of them are taken into account up to and including TODAY. What you're doing is something very different. You are filtering Dates AS VISIBLE IN THE CURRENT CONTEXT to only be those that are not in the future. This has by no means anything to do with "To Date." But I don't really know what it is you want to achieve since your pictures do not agree with your words...

[daxer-almighty]

[Bookings Budget UK (TD)] =
var MaxVisibleDate = MAX( Dates[Date] )
var Result =
    CALCULATE(
        [Bookings Budget UK],
        // Dates should be a date table
        // marked as such in the model.
        Dates[Date] <= MaxVisibleDate
    )
return
    Result
    
// Here's the same measure but you
// fix once and for all that the
// anchor date should be TODAY.
[Bookings Budget UK (TD)] =
var MaxVisibleDate = TODAY()
var Result =
    CALCULATE(
        [Bookings Budget UK],
        // Dates should be a date table
        // marked as such in the model.
        Dates[Date] <= MaxVisibleDate
    )
return
    Result

[LaurenceSD]

Thanks, I'll take a look at that formula, but seem error must have been something i did with another table with the relationship, rebuilding all the relationships seems to be working

[daxer-almighty]

Have you marked your Dates table as a date table in the model?

[LaurenceSD · Accepted solution]

I've not, but I didn't yesterday and it was working. I've now found a soution which was to delete all the relationships, then start to rebuild them, I've only linked up the Date and the Office table, but it's now working fine, I must have done something when linking a table up this morning somewhere