Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

calculate function not giving correct answer

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)?
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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's avatar
    v-yulgu-msft
    7 years ago

    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

5 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    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's avatar
      d_gosbell
      Super User
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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