Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

IF Statement help

Good afternoon,

Can someone tell me why this is not working?

 

IF (
YEAR( 'Casino Project Feb 2020'[Date] ) = YEAR (LASTDATE('Casino Project Feb 2020'[Date]))
&& MONTH( 'Casino Project Feb 2020'[Date]) = MONTH ( LASTDATE('Casino Project Feb 2020'[Date])),
"Yes",
"No"
)
 
I am trying to calculate MTD numbers without using a slicer (the higher ups do not want a slicer) based on the lastdate of the information in the table.  The reason I can't use it based on today is because for example, the last date of information I have in my table is Feb 29, 2020.  I want it to return MTD based on Feb. 29, 2020.  If I can get the yes/no's correct, then I can calculate the mtd by filtering.  Any help is greatly appreciated and if you have a better way, I am all ears.
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

     

    This is because lastdate returns the current row, you will need to add All(table) as the context:

     

    Column = 
var Lastdate_= CALCULATE(LASTDATE('Casino Project Feb 2020'[Date]),ALL('Casino Project Feb 2020'))
Return 
IF(YEAR( 'Casino Project Feb 2020'[Date] ) = YEAR (Lastdate_)
&& MONTH( 'Casino Project Feb 2020'[Date]) = MONTH (Lastdate_),
"Yes","No")

     

     

    Best regards 

    Paul Zheng

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Also, the calculated column is returning "yes" for all rows even when it should be "no"

  • ahmedoye's avatar
    ahmedoye
    Responsive Resident
    6 years ago

    Anonymous , try using this measure for a try:

    VAR SelectedYear = YEAR(SELECTEDVALUE(Casino Project Feb 2020'[Date] ))
    VAR SelectedMonth = YEAR(SELECTEDVALUE(Casino Project Feb 2020'[Date] ))
    VAR LatestDate = MAX('Casino Project Feb 2020'[Date])
    VAR LatestYear = YEAR(LatestDate)
    VAR LatestMonth = MONTH(LatestDate)

    RETURN IF(SelectedYear = LatestYear && SelectedMonth = LatestMonth, "Yes", "No")

    if this Solution works for you, kindly kudo and mark as solution to enable others benefit from it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      In the second line, do you mean:

      VAR SelectedMonth = MONTH(SELECTEDVALUE(Casino Project Feb 2020'[Date] ))

       

      I changed it to Month and now I am getting all "no"'s  so it still isn't doing it correctly....

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      ahmedoye, In the second line, do you mean:

      VAR SelectedMonth = MONTH(SELECTEDVALUE(Casino Project Feb 2020'[Date] ))

       

      I changed it to Month and now I am getting all "no"'s  so it still isn't doing it correctly....  Do you have any other thoughts?  Thank you so much for looking at this.

      • ahmedoye's avatar
        ahmedoye
        Responsive Resident
        6 years ago

        Anonymous , I figure you are doing this inside a Calculated Column. Edit your Initial DAX to look like this:

        IF (
        YEAR( 'Casino Project Feb 2020'[Date] ) = YEAR (LASTDATE(ALL('Casino Project Feb 2020'[Date])))
        && MONTH( 'Casino Project Feb 2020'[Date]) = MONTH (LASTDATE(ALL('Casino Project Feb 2020'[Date]))),
        "Yes",
        "No"
        )
         
        If this solution works for you, kindly give a kudo and mark it as the solution to enable others benefit from it.