cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaurenceSD
Helper II
Helper II

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

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
LaurenceSD_0-1626800670673.png

 

 
 

 

1 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

 

LaurenceSD_0-1626802212225.png

 

View solution in original post

5 REPLIES 5
daxer-almighty
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

[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

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
Solution Sage
Solution Sage

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

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

 

LaurenceSD_0-1626802212225.png

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors