Forum Discussion

lcap's avatar
lcap
New Member
3 years ago
Solved

DAX 'Current Month'

Hiya,   I'm struggling to work out how to formulate a column with an IF statement - i'm stuck how to express Current Month.   Thanks in advance.  
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Try:

    Before current month =
VAR _YMToday =
    YEAR ( TODAY () ) * 100
        + MONTH ( TODAY () )
VAR _YMField =
    YEAR ( MAX ( 'Dates Table'[Date] ) ) * 100
        + MONTH ( MAX ( 'Dates Table'[Date] ) )
RETURN
    IF ( _YMField = _YMToday, "Current month" )
PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

See my previous post: I edited it to be more precise....

 

7 Reactive Report Before Current Month? = IF([Case Reported On]<MONTH(TODAY()) && YEAR(TODAY(),"Yes","No")

You need to compare a month field with MONTH(TODAY()) and a year field with YEAR(TODAY()).

What is your [Case reported on] measure returning?

LYeo87's avatar
LYeo87
Helper II
3 years ago

[Case reported on]  is a date field.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Actually the code I posted will not return previous dates in previous years which are > MONTH (TODAY()).

    So you can either use:

    Before current month = 
VAR _YMToday = YEAR(TODAY()) * 100 + MONTH(TODAY())
VAR _YMField = YEAR(MAX('Dates Table'[Date])) * 100 + MONTH(MAX('Dates Table'[Date]))
RETURN
IF(_YMField < _YMToday, "Previous")

     

    or

    Alternative =
IF (
    MAX ( 'Dates Table'[Date] )
        < STARTOFMONTH (
            FILTER ( ALL ( 'Dates Table'[Date] ), 'Dates Table'[Date] = TODAY () )
        ),
    "Alternative"
)

     

  • LYeo87's avatar
    LYeo87
    Helper II
    3 years ago

    I already have a similar column which is working fine - 

    6 Reactive Report Before 2 Days Ago? = IF([Case Reported On]<TODAY()-2,"Yes","No")
    • LYeo87's avatar
      LYeo87
      Helper II
      3 years ago

      THank you so much.

       

      Is there a simple way to use an IF statement to say wether its in the current month?

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        Try:

        Before current month =
VAR _YMToday =
    YEAR ( TODAY () ) * 100
        + MONTH ( TODAY () )
VAR _YMField =
    YEAR ( MAX ( 'Dates Table'[Date] ) ) * 100
        + MONTH ( MAX ( 'Dates Table'[Date] ) )
RETURN
    IF ( _YMField = _YMToday, "Current month" )