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

How to create bins from a date column like last 1 month, last 3 months etc

Hi, 

 

I have a date column which has the list of 12 months of data like this: 

 

temp.PNG

And, I want this to split into bins like "Last 1 month", "Last 3 months", "Last 6 months", "Last 12 months",

temp2.PNG

so that for example: when I click on "Last 3 months", the data related to April, March and Feb 2019 should be displayed and when I click on "Last 6 months" - Data from November 2019 till April 2019 should be displayed. 

 

Appreciate if someone could help me on this! 

 

Thanks in advance,

Shashank. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: How to create bins from a date column like last 1 month, last 3 months etc

Hi @ShashankKalluri 

You may try to create a measure and use it in visual level filter.For further,please refer to attached sample file.

Measure = 
VAR EToday =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 31 )
VAR Edate1 =
    EDATE ( EToday, -1 )
RETURN
    IF (
        ISFILTERED ( Table1[Slicer] ),
        IF (
            MAX ( Table1[Slicer] ) = "Last 1 month"
                && MAX ( 'Date'[Date] ) <= Edate1
                && MAX ( 'Date'[Date] ) > EDATE ( EToday, -2 ),
            1,
            IF (
                MAX ( Table1[Slicer] ) = "Last 3 months"
                    && MAX ( 'Date'[Date] ) <= Edate1
                    && MAX ( 'Date'[Date] ) > EDATE ( EToday, -4 ),
                1,
                IF (
                    MAX ( Table1[Slicer] ) = "Last 6 months"
                        && MAX ( 'Date'[Date] ) <= Edate1
                        && MAX ( 'Date'[Date] ) > EDATE ( EToday, -7 ),
                    1,
                    IF (
                        MAX ( Table1[Slicer] ) = "Last 12 months"
                            && MAX ( 'Date'[Date] ) <= Edate1
                            && MAX ( 'Date'[Date] ) > EDATE ( EToday, -13 ),
                        1
                    )
                )
            )
        ),
        1
    )

Regards,

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

View solution in original post

6 REPLIES 6
Highlighted
Helper I
Helper I

Re: How to create bins from a date column like last 1 month, last 3 months etc

Hi You can use date slicer (Use Relative) Please find Attached PBIX File

 

https://app.powerbi.com/view?r=eyJrIjoiODU1ZjkyNWUtNGM1ZC00YTU5LTk0NWEtODc5ZTgwOTJiYTVhIiwidCI6IjdiZ...

 


 

Please Mark as a Solution if you found solution

Highlighted
Frequent Visitor

Re: How to create bins from a date column like last 1 month, last 3 months etc

Hey Vikram - Thanks for responding. However, that's not how I want.

 

I have month wise information, and my client wants to see it as temp2.PNGAppreciate if you can help me. 

 

Highlighted
Helper I
Helper I

Re: How to create bins from a date column like last 1 month, last 3 months etc

Hey, Thanks for reply,

 

then you can use BookMark

 

Create 4 Button and give name respectivly and apply filter according and apply bookmark setting.

 

you can find bookmark in "View" Tab

Please find below link

https://app.powerbi.com/view?r=eyJrIjoiODU1ZjkyNWUtNGM1ZC00YTU5LTk0NWEtODc5ZTgwOTJiYTVhIiwidCI6IjdiZ...

Please Mark as a Solution if it's help.

 

 

Thanks

 

Highlighted
Frequent Visitor

Re: How to create bins from a date column like last 1 month, last 3 months etc

I did think about Bookmarks, Vikram  however it is a brute solution to this problem, if you ask me. 

 

I have a good amount data in the background to process and four different bookmarks and overlapping all visuals will only make the dashboard slow. 

 

Was interested to know if there's a better and smart way of solving this problem. 

Highlighted
Microsoft
Microsoft

Re: How to create bins from a date column like last 1 month, last 3 months etc

Hi @ShashankKalluri 

You may try to create a measure and use it in visual level filter.For further,please refer to attached sample file.

Measure = 
VAR EToday =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 31 )
VAR Edate1 =
    EDATE ( EToday, -1 )
RETURN
    IF (
        ISFILTERED ( Table1[Slicer] ),
        IF (
            MAX ( Table1[Slicer] ) = "Last 1 month"
                && MAX ( 'Date'[Date] ) <= Edate1
                && MAX ( 'Date'[Date] ) > EDATE ( EToday, -2 ),
            1,
            IF (
                MAX ( Table1[Slicer] ) = "Last 3 months"
                    && MAX ( 'Date'[Date] ) <= Edate1
                    && MAX ( 'Date'[Date] ) > EDATE ( EToday, -4 ),
                1,
                IF (
                    MAX ( Table1[Slicer] ) = "Last 6 months"
                        && MAX ( 'Date'[Date] ) <= Edate1
                        && MAX ( 'Date'[Date] ) > EDATE ( EToday, -7 ),
                    1,
                    IF (
                        MAX ( Table1[Slicer] ) = "Last 12 months"
                            && MAX ( 'Date'[Date] ) <= Edate1
                            && MAX ( 'Date'[Date] ) > EDATE ( EToday, -13 ),
                        1
                    )
                )
            )
        ),
        1
    )

Regards,

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

View solution in original post

Highlighted
Frequent Visitor

Re: How to create bins from a date column like last 1 month, last 3 months etc

 @v-cherch-msft It works perfect buddy! thank you very very much! 🙂 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors