cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

CALENDAR DAX: Starting on 01/01/2017 and finishing in the last day of previous month

Hi All, 

 

I have a calender table create based on DAX : 

CALENDAR = CALENDAR ("01/01/2017", today())

But what I would like is to have the "end date" of the calendar as the last day of previous month. Example: 

 

Today is 11/02/2019, on my calendar I'd like to have all dates between 01/01/2017 and 31/01/2019. I'd like to create this in a DAX to not amend the end of calendar each month. 

 

It's necessary, because my database is based on the previous month, and I dont want to give for the End user the option on my slicer of the current month as the data wont be there until the end of the month. 

 

I was trying to use DATEADD function but it had no sucess. 

 

Any clue how can I have in a DAX the return of the last day of previous month based on today to create my autocalendar ?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: CALENDAR DAX: Starting on 01/01/2017 and finishing in the last day of previous month

Hi @Rich_coutinho

 

Try this:

 

CalendarTable =
CALENDAR ( "01/01/2017", EOMONTH ( TODAY (), -1 ) )

View solution in original post

1 REPLY 1
Highlighted
Super User III
Super User III

Re: CALENDAR DAX: Starting on 01/01/2017 and finishing in the last day of previous month

Hi @Rich_coutinho

 

Try this:

 

CalendarTable =
CALENDAR ( "01/01/2017", EOMONTH ( TODAY (), -1 ) )

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors