Last 12 months visual filter according to month and year slicer

[D1ltang]

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

[D1ltang]

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

[Eric_Zhang]

@D1ltang

See my demo in the attached pbix file.

[D1ltang]

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

[Eric_Zhang · Accepted solution]

---

@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 ()
)

[BLemay]

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.

[D1ltang]

Thanks Eric. This works exactly as I need