Forum Discussion

Mith's avatar
Mith
Regular Visitor
3 years ago
Solved

Current Month vs Previous Month

Hi,

 

I was using the below Dax formula for current month and previous month month last 9 months and giving me the accurate answer. Today i noticed after refreshed the data, in my report for CM and PM all value is showing 0. i could not figure it out. 

 

Would yo please help me to write dax. please see the DAX which i used last few months.

 

Thanks

 

Current month Female=

VAR_current month=Month(Today())

return

calculate([Female_App],filter(Applicant,Month(Applicant[Added Date]=current month-1))

 

 

Previous month=

VAR_current month=Month(Today())

return

calculate([Female_App],filter(Applicant,Month(Applicant[Added Date]=current month-2))

Need Help
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    This should work now.

    Current month Female =
VAR _current_month =
    MONTH ( EOMONTH ( TODAY (), -1 ) )
VAR _current_year =
    Year ( EOMONTH ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        [Female_App],
        FILTER (
            Applicant,
            INT(MONTH ( Applicant[Added Date] )) = INT(_current_month)
                && INT(YEAR ( Applicant[Added Date] )) = INT(_current_year)
        )
    )
    previous month Female =
VAR _current_month =
    MONTH ( EOMONTH ( TODAY (), -2 ) )
VAR _current_year =
   Year ( EOMONTH ( TODAY (), -2 ) )
RETURN
    CALCULATE (
        [Female_App],
        FILTER (
            Applicant,
            INT(MONTH ( Applicant[Added Date] )) = INT(_current_month)
                && INT(YEAR ( Applicant[Added Date] )) = INT(_current_year)
        )
    )

     

8 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi Mith ,

     

    If I understand your question correctly then below could be your ideal code:-

    Current month Female =
VAR _current_month =
    MONTH ( EOMONTH ( TODAY (), -1 ) )
VAR _current_year =
    Year ( EOMONTH ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        [Female_App],
        FILTER (
            Applicant,
            MONTH ( Applicant[Added Date] ) = _current_month
                && YEAR ( Applicant[Added Date] ) = _current_year
        )
    )
    previous month Female =
VAR _current_month =
    MONTH ( EOMONTH ( TODAY (), -2 ) )
VAR _current_year =
   Year ( EOMONTH ( TODAY (), -2 ) )
RETURN
    CALCULATE (
        [Female_App],
        FILTER (
            Applicant,
            MONTH ( Applicant[Added Date] ) = _current_month
                && YEAR ( Applicant[Added Date] ) = _current_year
        )
    )
    • Mith's avatar
      Mith
      Regular Visitor
      3 years ago

      Thanks for your reply. but i am getting the below error-

       

      calculation error in measure "Applicant[current month female applicant]:Dax comparison operations do no support comparing values of types text with values of type integer. consider using the value or format function to convert one of the values.

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        3 years ago

        Mith ,

         

        Please try this:-

        Current month Female =
VAR _current_month =
    INT(MONTH ( EOMONTH ( TODAY (), -1 ) ))
VAR _current_year =
    INT(Year ( EOMONTH ( TODAY (), -1 ) ))
RETURN
    CALCULATE (
        [Female_App],
        FILTER (
            Applicant,
            MONTH ( Applicant[Added Date] ) = _current_month
                && YEAR ( Applicant[Added Date] ) = _current_year
        )
    )
        previous month Female =
VAR _current_month =
    INT(MONTH ( EOMONTH ( TODAY (), -2 ) ))
VAR _current_year =
   INT(Year ( EOMONTH ( TODAY (), -2 ) ))
RETURN
    CALCULATE (
        [Female_App],
        FILTER (
            Applicant,
            MONTH ( Applicant[Added Date] ) = _current_month
                && YEAR ( Applicant[Added Date] ) = _current_year
        )
    )