End of Month If statement formula

[gmasta1129]

Hello,

I have a column (titled "PE Date") which consists of dates (ex: 1/2/2022, 1/20/2022 1/31/2022 etc).  

I am trying to create an if statement where if the date is the last day of the month (ex: 12/31/, 1/31, 2/28, 3/31, 4/30 etc.) then pull in the word "Yes" and if not then "No".  

[gmasta1129 · Accepted solution]

Hello, 

thanks for the response but what i need is for the formula to say yes its the last day of the month or no it is not the last day of the month.  Your formula above finds each day in the month and pulls in the last day of the month.  

what i did was changed the format of the pe date column by using the VALUE function. This turned 11/30/21 to 44530.    then used a switch statement where if equal to 44530 then "yes", if not then "no". 

[v-yangliu-msft · Accepted solution]

Hi  @gmasta1129 ,

Here are the steps you can follow：

1. Create calculated column.

Column =
IF('Table'[PE Date]=EOMONTH('Table'[PE Date],0),"Yes","No")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[gmasta1129 · Accepted solution]

Hello, 

thanks for the response but what i need is for the formula to say yes its the last day of the month or no it is not the last day of the month.  Your formula above finds each day in the month and pulls in the last day of the month.  

what i did was changed the format of the pe date column by using the VALUE function. This turned 11/30/21 to 44530.    then used a switch statement where if equal to 44530 then "yes", if not then "no". 

[MahyarTF]

Hi,

Use below Dax code for creating the column and use it in visuals :

[amitchandak]

@gmasta1129 , you can use closingbalancemonth

https://youtu.be/yPQ9UV37LOU

or with a month year column

calculate(lastnonblankvalue('Table'[Month Year], Sum(Table[Value]) )

calculate(lastnonblankvalue('Table'[Month Year], Sum(Table[Value]) , allexcept(Table, Table[Month Year]) )