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

[carlossantiago]

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

[OwenAuger · Accepted solution]

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

[OwenAuger · Accepted solution]

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

[nickchobotar]

Hello  @OwenAuger

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

Thanks, N -

[OwenAuger]

@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.

[nickchobotar]

Very cool! Thank you @OwenAuger

[carlossantiago]

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

[OwenAuger]

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

[carlossantiago]

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

[OwenAuger · Accepted solution]

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)