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

[ShashankKalluri]

Hi, 

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

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

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. 

[v-cherch-msft · Accepted solution]

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,

[ShashankKalluri]

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

[Vikram123]

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

[ShashankKalluri]

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 Appreciate if you can help me. 

[Vikram123]

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

[ShashankKalluri]

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.