how to get top two months from a date column as filter ?

[AnandRanga]

I am having a date field in which I have all the dates. I want to get only current month and previous month name as filter. How to do this ? 

I have tried max but it returns only current month number. Will TopN will work here, If yes can anyone tell me how?

Thanks for your time.

[v-yulgu-msft]

Hi @AnandRanga,

You want to filter data, right? Please try the below formula to create a calculate table:

NewTable =
CALCULATETABLE (
    TopTwoMonth,
    FILTER (
        TopTwoMonth,
        TopTwoMonth[Date].[MonthNo] = MONTH ( TODAY () )
            || TopTwoMonth[Date].[Year]
                = IF (
                    MONTH ( TODAY () ) = 1,
                    YEAR ( TODAY () ) - 1,
                    YEAR ( TODAY () )
                        && TopTwoMonth[Date].[MonthNo]
                            = IF ( MONTH ( TODAY () ) = 1, 12, MONTH ( TODAY () ) - 1 )
                )
    )
)

If you have any question, please feel free to ask.

Thanks,
Yuliana Gu

[AnandRanga]

I will try your solution  and will let you know baut for now I tried this and it is showing current and  previous month but It is showing (blank) also. Transdate is the column contains all the dates.

MonthsNEW = IF(MONTH(TIMESHEETANALYSISCUBE[TRANSDATE])=Month(NOW()), "Current Month",IF(MONTH(TIMESHEETANALYSISCUBE[TRANSDATE])=Month(NOW())-1,"Previous month")) 

Actually it is showing current and previous month but except these two months all the months are coming into (blank).

Is there any way to remove it ?

Thanks for your time.

[v-yulgu-msft · Accepted solution]

Hi @AnandRanga,

To remove the records in which MonthsNEW is blank, you can create a calculate table:

Table =
    CALCULATETABLE ( TIMESHEETANALYSISCUBE, TIMESHEETANALYSISCUBE[MonthsNEW] <> BLANK () )

Thanks,
Yuliana Gu