Forum Discussion

KW123's avatar
KW123
Icon for Helper V rankHelper V
4 years ago

Previous Month

Hi, 

I need to get the MAX (or last value) of a previous month for a measure [WhatdoesYTDtotalneedtobe]

I have tried using this DAX as both a measure and a column, but it only returns blanks. 

 
CALCULATE([What does YTD total need to be],
PREVIOUSMONTH('Dates'[Date]))

I have also tried

var current_month= MONTH(TODAY()) return  CALCULATE(SUM('2022 GOals'[WhatdoesYTDtotalneedtobe]),FILTER('Dates',MONTH('Dates'[Month])=current_month -1))

Any ideas? 

Thanks!

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    KW123 Hard to say without actual example data. You can get last month by using EOMONTH([Date], -1) that will get you the last day of the month, is that what you want? Or do you want to get MAXX of some value that exists in the last month?

    • KW123's avatar
      KW123
      Icon for Helper V rankHelper V
      4 years ago

      Greg_Deckler Thanks for the reply! 

      I have a separate column already with the previous days.  I'm looking for a measure which will return the last day (is the same as the max) $ value from the previous month. 

      My DAX measure for the column I need is 

      What does YTD total need to be =
      VAR A = [Accounting goal calc-c]
      VAR B = [Daily Goal]
      VAR CurrentIndex =
      SELECTEDVALUE ( Dates[FD2] )
      VAR MaxIndex =
      CALCULATE ( MIN ( Dates[FD2] ), ALLEXCEPT ( Dates, Dates[Date] ) )
      RETURN
      IF (
      DAY ( SELECTEDVALUE ( Dates[Date] ) ) = 1
      && MONTH ( SELECTEDVALUE ( Dates[Date] ) ) = 1,
      0,

      IF ( CurrentIndex <> BLANK (), A + ( MaxIndex - CurrentIndex ) * B )
      )
      Basically a running total based on the day of the week.  I need the MAX value to be able to use it in another measure to calculate something for a separate column.  I hope that helps a bit more. 

  • KW123's avatar
    KW123
    Icon for Helper V rankHelper V
    4 years ago

    I created a column which shows me the last day of the previous month: 


    Last day of previous month =
    PREVIOUSMONTH('Dates'[Last day of month])

    However when I try to use it in a calculate measure this is the error: 

    A Function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression.  This is not allowed.' 

    The DAX I am using: 
    Lastdayprevmonthvalue =
    CALCULATE([What does YTD total need to be],'2022 Goals'[Last day of previous month])



  • KW123's avatar
    KW123
    Icon for Helper V rankHelper V
    4 years ago

    If I use this DAX: 

    Prevmonth =
    CALCULATE(
    SUMX(
    '2022 Goals',
    '2022 Goals'[What does YTD total need to be]
    ),
    FILTER(
    ALL(Dates),
    Dates[Year] = MAXX (Dates, Dates[Year]) &&
    Dates[Month] = MAXX(Dates,Dates[Month]) -1
    ))

    I still get a blank result.  However if I add
     
    &&
    Dates[Date] = MAXX(Dates,Dates[Date]) -1

    then it gives me the max of the current month but blanks for the first and last day of that month.  So I am assuming it's -1 of the day rather than the month.  

    The Date table shares a relationship by month name to the goals table.  I have tried using [Month name] instead but no result.