cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carlossantiago
Frequent Visitor

How do I display the text "current month" in a month filter?

Hi guys,

 

I have a month filter in my dashboard . I'd like to display "current month" instead of jun 2017 for the last month. How do I do that?

 

Thanks,

 

Carlos

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

Hi @carlossantiago

 

I would add a column to your Calendar table that contains month-year text for all months except the "current" month where it contains "Current Month".

 

Also, ensure that this column is sorted by a numerical month-year column.

 

Here is an example of a Calendar table created with DAX but probably should be done with M.

 

Calendar = 
VAR StartDate =
    DATE ( 2016, 1, 1 )
VAR EndDate = 
    DATE ( 2017, 06, 30 )
VAR YearMonthNumberFinal =
    YEAR ( EndDate ) * 12 + MONTH ( EndDate ) - 1
    // This sets the final YearMonth based on the last date in the calendar.
    // You could define however you want, e.g. based on TODAY()
VAR BaseCalendar =
    CALENDAR ( StartDate, EndDate )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR MonthName = FORMAT ( BaseDate, "mmmm" )
        VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
        RETURN ROW (
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", MonthName,
            "Year Month Number", YearMonthNumber,
            "Year Month", YearMonthName,
            "Year Month with Current Month", IF ( YearMonthNumber = YearMonthNumberFinal, "Current Month", YearMonthName ) 
        )
    )

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

No problem!

 

For text columns, you just need to specify an appropriate "Sort by" column.

 

In the case of "Year Month with Current Month", select it in either the field list or Data View, then go Modelling => Sort by Column and choose Year Month Number.

 

(This is already done in the dropbox link I posted)


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

8 REPLIES 8
OwenAuger
Super User
Super User

Hi @carlossantiago

 

I would add a column to your Calendar table that contains month-year text for all months except the "current" month where it contains "Current Month".

 

Also, ensure that this column is sorted by a numerical month-year column.

 

Here is an example of a Calendar table created with DAX but probably should be done with M.

 

Calendar = 
VAR StartDate =
    DATE ( 2016, 1, 1 )
VAR EndDate = 
    DATE ( 2017, 06, 30 )
VAR YearMonthNumberFinal =
    YEAR ( EndDate ) * 12 + MONTH ( EndDate ) - 1
    // This sets the final YearMonth based on the last date in the calendar.
    // You could define however you want, e.g. based on TODAY()
VAR BaseCalendar =
    CALENDAR ( StartDate, EndDate )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR MonthName = FORMAT ( BaseDate, "mmmm" )
        VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
        RETURN ROW (
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", MonthName,
            "Year Month Number", YearMonthNumber,
            "Year Month", YearMonthName,
            "Year Month with Current Month", IF ( YearMonthNumber = YearMonthNumberFinal, "Current Month", YearMonthName ) 
        )
    )

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hello  @OwenAuger

 

 

This  YearMonthNumberFinal   YEAR ( EndDate ) * 12 + MONTH ( EndDate ) - 1 , what is this number you are generating?

 

Thanks, N -

@nickchobotar

I should mention that the pattern in the DAX code was borrowed from 

http://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/

 

YearMonthNumber is an integer that increments by one for each month.

It can be useful as a "sort by" column for months, or for calculating differences between dates in whole numbers of months.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Very cool! Thank you @OwenAuger

Thanks Owen but it is not working.  I got the message: "Multiple Columns can`t be converted to scalar value"

 

 

It sounds like you tried to create a measure with this code rather than a calculated table.

 

Go to Modelling tab => New Table and paste in the code.

 

Here is a sample in case it helps:

https://www.dropbox.com/s/wnll2q2glarwbb0/Calendar%20with%20current%20month%20sample.pbix?dl=0

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks Owen!

 

It`s working but not sorting by date because the data have to be classified as text instead of date. Do you know how to solve that?

I wish to use the 'Year Month with Current Month' to create a filter.

 

 

Many Thanks

 

 

No problem!

 

For text columns, you just need to specify an appropriate "Sort by" column.

 

In the case of "Year Month with Current Month", select it in either the field list or Data View, then go Modelling => Sort by Column and choose Year Month Number.

 

(This is already done in the dropbox link I posted)


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.