cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Last Day of Each Month-Year

The dataset has every single date from early 2017 through now (late 2019), the goal is to create a calculated field to indicate the last day of each month of all years in the dataset. The calculation that I have so far returns the last day of the most recent 12 months, but it is missing the months from the other years. What do I need to change?

Lastest Date =
VAR currentMonth =
MONTH([DATE_STAMP])
VAR latestDateofMonth =
CALCULATE (
MAX([DATE_STAMP]),
FILTER (ALL (student_status_count), MONTH([DATE_STAMP]) = currentMonth)
)
RETURN
IF ( [DATE_STAMP] = latestDateofMonth, 1, 0 )

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Last Day of Each Month-Year

@diogobraga2 this can work

``````Is this last date of the month =
IF ( Table[Date] = EOMONTH ( Table[Date], 0 ), 1, 0 )``````

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

3 REPLIES 3
Super User

## Re: Last Day of Each Month-Year

@diogobraga2 this can work

``````Is this last date of the month =
IF ( Table[Date] = EOMONTH ( Table[Date], 0 ), 1, 0 )``````

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Regular Visitor

## Re: Last Day of Each Month-Year

@parry2k, your solution led to this other problem:

https://community.powerbi.com/t5/Desktop/Last-Day-Available-for-Each-Month/m-p/854569#M410054

Let me know if you have any feedback.

Super User

## Re: Last Day of Each Month-Year

Hi,

Create a Calendar Table and build a relationship from the Date column of the base data table to the Date column of the Calendar Table.  In the Calendar Table, create a column for Year and Month.  Build a matrix visual and drag Year and Month there.  Write this measure

=MAX(Calendar[Date])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)