Getting unexpected exception with measure

[Jean_Ferro]

Hello everyone!

I am creating this measure that works as needed only if I keep the date fixed like DATE(2024, 6, 10), if I try to make the date dynamic using a measure that returns the greatest date chosen by the user in the calendar like this:

maxx(dCalendar,[Date])

I get the error "unexpected exception"

SUMX(
ADDCOLUMNS(
SUMMARIZE(
FILTER(
fStock,
fStock[DTABASE] <= DATE(2024, 6, 10)
),
fStock[PRODUCT_CODE],
fStock[BRANCH],
"MaxDate", MAX(fStock[DTABASE])
),
"Cost",
CALCULATE(
MAX(fStock[COST]),
FILTER(
fStock,
fStock[DTABASE] = [MaxDtaEntradaSaida] &&
fStock[PRODUCT_CODE] = EARLIER(fStock[PRODUCT_CODE]) &&
fStock[BRANCH] = EARLIER(fStock[BRANCH])
)
)
),
[Cost]
)

[Greg_Deckler]

@Jean_Ferro What about just MAX('dCalendar'[Date])

or:

Measure = 
VAR __Date = MAX('dCalendar'[Date])
VAR __Result = 
SUMX(
ADDCOLUMNS(
SUMMARIZE(
FILTER(
fStock,
fStock[DTABASE] <= __Date
),
fStock[PRODUCT_CODE],
fStock[BRANCH],
"MaxDate", MAX(fStock[DTABASE])
),
"Cost",
CALCULATE(
MAX(fStock[COST]),
FILTER(
fStock,
fStock[DTABASE] = [MaxDtaEntradaSaida] &&
fStock[PRODUCT_CODE] = EARLIER(fStock[PRODUCT_CODE]) &&
fStock[BRANCH] = EARLIER(fStock[BRANCH])
)
)
),
[Cost]
)
RETURN
  __Result

[Jean_Ferro]

Unfortunately the same unexpected exception error, if I change it to DATE(2024,6,5) for example it works, but I need it to be dynamic.

The measure to return the greatest date seems correct to me:

GreatestDataStock = maxx(dCalendar,[Date])

[Greg_Deckler]

@Jean_Ferro Any chance you can post a link to the file? Otherwise it's really hard to understand what is going on here.

[Jean_Ferro]

I was preparing a file to upload with just the information to reproduce the error but when I tested it on this test file it worked.

In my production development I use two databases communicating by direct query sharing 1 calendar, when I separated the calendars leaving one for each database it worked.

I'm going to use it like this for now, but I would like to find a way to use a calendar for both databases without the error.

[Greg_Deckler]

@Jean_Ferro You may want to play around with privacy level settings then. Depending on those settings, some cross query/source queries aren't allowed.

[Jean_Ferro]

With this method, i change privacy level for this on all file involved:

Is here?

Still dosent work.

Only works on this way:

[Greg_Deckler]

@Jean_Ferro Yep, that's what I was referring to. Interesting, let me do some research as maybe there is some kind of formula firewall thing going on here.

[Jean_Ferro]

I tried this way too and it still doesn't work.

I really appreciate your attention and help, I will continue working in the way it is working, if you have any more ideas I would be very happy if you shared them!