cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnandRanga Member
Member

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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AnandRanga Member
Member

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

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.

Community Support Team
Community Support Team

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.