cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
diogobraga2 Regular Visitor
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 )

2019-11-22_1518.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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 )

 






Did I answer your question? Mark my post as a solution.

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





View solution in original post

3 REPLIES 3
Super User
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 )

 






Did I answer your question? Mark my post as a solution.

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





View solution in original post

diogobraga2 Regular Visitor
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
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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

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

November 2019 Community Highlights

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

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)