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

Last 12 months visual filter according to month and year slicer

Hi I have a table visual which displays Month - Year (mmm-dd) and average payload.

 

| Month        | Payload        |

| m - 12        |  x                  |

|.                   |

|.                   |

|.                   |

|.                   |

|selected Month by slicer | x for that month            <-------

 

I have two slicers, Month and Year which are from my date dimension.

 

I have a calculated Rank Column in my Date Dimension table:

 

MTC Rank = RANKX('Working Date','Working Date'[FullDate],,asc).

 

On my table visual I have used the visual level feature saying top 11 month sorted by rank month..

 

Without selecting anything from my slicers, I get the the desired out come. My issue is that when I make a slicer selection I either only see dates, for that said month or year. I am aware that my Rank function does not take into account slicer context but I am not sure how to make it behave in that manner.

 

The selected Month/Year should appear at the bottom of my table visual and above it I should see the previous 11 months.

E.g If I select October 2017

starting from the top of the table to the bottom, I should see November 2016, December 2016 ....... October 2017.

 

I have been through numerous posts on the forum and the closest I have found to what I need can be found here:

https://community.powerbi.com/t5/Community-Knowledge-Base/Rolling-N-Dates-based-on-slicer-selection/...

 

Thanks in advance for anyone who might be able to help or point me in the right direction

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Dynamic Display Last 12 Months in Visual According to Month and Year Slicer


@D1ltang wrote:

Hi @Eric_Zhang,

 

First of all thank you for taking the time and effort to reply. From your demo I can see that  you  have approached this the same way I have. What I am not sure how to do and have tested this against your dashboard is:

 

If you select Year: 2017, Month January and TopN as 2.

 

What I need to see in my visual is :

 

|| MonthKey   || Measure     ||

|| 01/12/2016 || someValue ||

|| 01/01/2017 || someValue ||

 

Do you have any ideas as to how your example can be modified to acheive this?

 

Thanks,

 

Dil


@D1ltang

It shall work. The measure is like

Measure = 
IF (
    AND (
        MAX ( yourTable[Month Key] ) <= MAX ( 'dim date'[Month Key] ),
        MAX ( yourTable[Month Key] )
            >= DATE ( YEAR ( MAX ( 'dim date'[Month Key] ) ), MONTH ( MAX ( 'dim date'[Month Key] ) ) - MAX ( 'TopN'[N] )+1, 1 )
    ),
    SUM ( yourTable[value] ),
    BLANK ()
)

View solution in original post

6 REPLIES 6
Highlighted
Frequent Visitor

Dynamic Display Last 12 Months in Visual According to Month and Year Slicer

Hi I have a table visual which displays Month - Year (mmm-dd) and average payload.

 

| Month        | Payload        |

| m - 12        |  x                  |

|.                   |

|.                   |

|.                   |

|.                   |

|selected Month by slicer | x for that month            <-------

 

I have two slicers, Month and Year which are from my date dimension.

 

I have a calculated Rank Column in my Date Dimension table:

 

MTC Rank = RANKX('Working Date','Working Date'[FullDate],,asc).

 

On my table visual I have used the visual level feature saying top 11 month sorted by rank month..

 

Without selecting anything from my slicers, I get the the desired out come. My issue is that when I make a slicer selection I either only see dates, for that said month or year. I am aware that my Rank function does not take into account slicer context but I am not sure how to make it behave in that manner.

 

The selected Month/Year should appear at the bottom of my table visual and above it I should see the previous 11 months.

E.g If I select October 2017

starting from the top of the table to the bottom, I should see November 2016, December 2016 ....... October 2017.

 

I have been through numerous posts on the forum and the closest I have found to what I need can be found here:

https://community.powerbi.com/t5/Community-Knowledge-Base/Rolling-N-Dates-based-on-slicer-selection/...

 

Thanks in advance for anyone who might be able to help or point me in the right direction

Highlighted
Microsoft
Microsoft

Re: Dynamic Display Last 12 Months in Visual According to Month and Year Slicer

@D1ltang

See my demo in the attached pbix file.

topn.gif

Highlighted
Frequent Visitor

Re: Dynamic Display Last 12 Months in Visual According to Month and Year Slicer

Hi @Eric_Zhang,

 

First of all thank you for taking the time and effort to reply. From your demo I can see that  you  have approached this the same way I have. What I am not sure how to do and have tested this against your dashboard is:

 

If you select Year: 2017, Month January and TopN as 2.

 

What I need to see in my visual is :

 

|| MonthKey   || Measure     ||

|| 01/12/2016 || someValue ||

|| 01/01/2017 || someValue ||

 

Do you have any ideas as to how your example can be modified to acheive this?

 

Thanks,

 

Dil

Highlighted
Microsoft
Microsoft

Re: Dynamic Display Last 12 Months in Visual According to Month and Year Slicer


@D1ltang wrote:

Hi @Eric_Zhang,

 

First of all thank you for taking the time and effort to reply. From your demo I can see that  you  have approached this the same way I have. What I am not sure how to do and have tested this against your dashboard is:

 

If you select Year: 2017, Month January and TopN as 2.

 

What I need to see in my visual is :

 

|| MonthKey   || Measure     ||

|| 01/12/2016 || someValue ||

|| 01/01/2017 || someValue ||

 

Do you have any ideas as to how your example can be modified to acheive this?

 

Thanks,

 

Dil


@D1ltang

It shall work. The measure is like

Measure = 
IF (
    AND (
        MAX ( yourTable[Month Key] ) <= MAX ( 'dim date'[Month Key] ),
        MAX ( yourTable[Month Key] )
            >= DATE ( YEAR ( MAX ( 'dim date'[Month Key] ) ), MONTH ( MAX ( 'dim date'[Month Key] ) ) - MAX ( 'TopN'[N] )+1, 1 )
    ),
    SUM ( yourTable[value] ),
    BLANK ()
)

View solution in original post

Highlighted
Frequent Visitor

Re: Dynamic Display Last 12 Months in Visual According to Month and Year Slicer

Thanks Eric. This works exactly as I need
Highlighted
Frequent Visitor

Re: Dynamic Display Last 12 Months in Visual According to Month and Year Slicer

Could it be possible to do the same thing but with the years? in a report I'm currently working on, I need to select a year to present only one year on multiple visual, but for few visual I would like to presente the previous year in the format:

 

If I select 2018 on my date slicer

 

Year | Sales

2018 | ....

2017 | ....

 

and if I select 2017 on my slicer it would become

 

Year | Sales

2017 | ....

2016 | ....

 


My slicer is on my date table which is link to my sales table. So when I've tried to tweak your formula it still only show me the year on my slicer.

 

Thx.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors