Subtract 1 month of the Calendar Function End Date

user_18177289 0
Viewed 1318

I am using a calendar table in an slicer in my Power BI report. In order to avoid the user to select the current month, I would like this calendar to go up to the last day of the previous month. i.e., If we are in February, it would only display the dates until Jan 31.

How can I define the end date of my Calendar DAX.

    CALENDAR (
              DATE(2019,1,1), 
              TODAY()
            )

I have tried a couple of things included ENDOFMONTH() and EOMONTH(), but none of them worked.

dax powerbi-desktop
2 Answers

If you want to exclude, then you could calculate the end date of the previous month using EOMONTH function and then pass this date to CALENDAR, e.g. like this:

var CalendarTable = 
var LastMonthEnd = EOMONTH(TODAY(),-1)
var DatesRange = CALENDAR (
              DATE(2019,1,1), 
              LastMonthEnd
            )
return DatesRange
user_86493930

I like Andrey's solution, but want to flagg that you can do it like this as well:

Calendar = 
CALENDAR(
    DATE(2019,1,1),
    TODAY()-DAY(TODAY())
)

As always, in DAX there are several ways of doing the same thing.

user_107744580
Related