calculate function not giving correct answer

[Anonymous]

I have a VERY simple formula:

MaxMonth = calculate(max('Fact Table'[CurrentMonth]), filter('Fact Table', max('Fact Table'[RefNum])))

I need it to find the max "RefNum" and lookup the value in the "CurrentMonth" column.

-CurrentMonth is a whole number based off the date pulled out of a SQL query

-RefNum is a whole number that starts at the earliest date and adds 1 every month there's new data.

What I need is to find the latest month of data in number format.

So the date is currently Feb-19-2019, which means my SQL query pulls data upto and including Jan-31-2019. This makes my max(RefNum) = 23 (as my dataset started in March-2017) and CurrentMonth = 1.

So the formula above... should it not filter my fact table for RefNum = 23, then give me 1 (since that's the only value in CurrentMonth when RefNum = 23)?

[d_gosbell · Accepted solution]

If you just want to lookup the value for the max RefNum do you need to a max of the CurrentMonth column or could you just use the LookupValue function?

MaxMonth = LookupValue(,'Fact Table', 'Fact Table'[CurrentMonth]), max('Fact Table'[RefNum]))

Or if you have mulitple rows with the same RefNum you might need to do something like the following:

MaxMonth =
VAR _maxRefNum = max('Fact Table'[RefNum])
RETURN CALCULATE( MAX( 'Fact Table'[CurrentMonth]), 'Fact Table'[RefNum] = _maxRefNum )

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

You could display the returned result of below measure in a card visual.

MonNo for max refNo =
VAR Curr_Date =
    TODAY ()
VAR Last_day_of_currMon =
    EOMONTH ( Curr_Date, 0 )
RETURN
    IF (
        Curr_Date < Last_day_of_currMon,
        MONTH ( EOMONTH ( Curr_Date, -1 ) ),
        MONTH ( Last_day_of_currMon )
    )

Best regards,

Yuliana Gu

[HotChilli]

The second parameter on your FILTER is not the expression you want, it should be(quote)

- 'A Boolean expression that is to be evaluated for each row of the table. For example, [Amount] > 0 or [Region] = "France" '

[d_gosbell · Accepted solution]

If you just want to lookup the value for the max RefNum do you need to a max of the CurrentMonth column or could you just use the LookupValue function?

MaxMonth = LookupValue(,'Fact Table', 'Fact Table'[CurrentMonth]), max('Fact Table'[RefNum]))

Or if you have mulitple rows with the same RefNum you might need to do something like the following:

MaxMonth =
VAR _maxRefNum = max('Fact Table'[RefNum])
RETURN CALCULATE( MAX( 'Fact Table'[CurrentMonth]), 'Fact Table'[RefNum] = _maxRefNum )

[Anonymous]

The lookupvalue function worked perfectly, THANK YOU!  I'm still a little unsure as to why the calculate function doesn't work though...

[d_gosbell]

---

@Anonymous wrote:

The lookupvalue function worked perfectly, THANK YOU!  I'm still a little unsure as to why the calculate function doesn't work though...

---

The problem with the original calculate is the call to FILTER(). Filter expects 2 parameters, a table to iterate over and a boolean expression, but your call to it looked like the following

 filter('Fact Table', max('Fact Table'[RefNum]))

With a table and a numeric expression. What happens in this case is that the forumla engine attempts to do an implied cast on max('Fact Table'[RefNum]) to convert the number to a boolean value. And the way it does this is to treat values of 0 as false and non-zero as true. So this will effectively not filter any rows.

You should be able to fix this by comparing the current value of the [RefNum] with the max value by adding the following bold code.

MaxMonth = calculate(max('Fact Table'[CurrentMonth]), filter('Fact Table', 'Fact Table'[RefNum] = max('Fact Table'[RefNum])))