IF Statement help

[sschmidt]

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.

[ahmedoye]

@sschmidt , 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.

[sschmidt]

@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.

[V-pazhen-msft · Accepted solution]

@sschmidt 

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

[ahmedoye]

@sschmidt , 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.

[sschmidt]

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....

[sschmidt]

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