Need to create fixed periods date slicer (Text)

[hemantsingh]

Hi,

     I am looking to create a list slicer for a fixed no. of selectors. It is basically a time slicer wherin clients wants to select from the choices instead of using relative slicer which almost do all the things required by the client. Client is kind of adamant for this requirement.  

       Slicer should look like this :

1. Sales in last 3 Month

2. Sales in last 6 Month

3. Sales in Last 12 Month

4. Sales in Last 24 month

5. Sales in Last 36 Month

    Largely this is exacly what relative slicer for date offers. This slicer not only filters the sales qty visual but shoould also filter all the visual available on the report correspondingly. I am running out of ideas as how to start this. Tried thinking of creating different measure & then puttng them in a visual filter but i am still searching for some good resource for doing it.

  Any ideas guys???

Regards,

Hemant

[iamprajot]

First, Create a Ranking table with Columns, Text & Value as follows,

Rank_Text - Top 6, Top 12, Top 18

Rank_Value - 6, 12, 18

and so on

Then, Create a Measure,

Selected = IF ( HASONEVALUE ( Rank_Text) ,VALUE ( Rank_Value ) , 0 )

Now, create another Measure,

Total Cost based on Product =
CALCULATE(SUM(TableName[Sales]),

FILTER(VALUES(TableName[Product]),

RANKX(ALL(TableName[Product]),SUM(TableName[Sales],,DESC) <= [Selected])))

Change

TableName with actual Table Name,

Product with actual Column Name by which you want to see and

Sales with actual column with Sales figures.

Create a Table using Product and Sum of Sales

and Drag Rank_Text of Ranking Table to a Slicer and there you go.

[hemantsingh]

hi @iamprajot,

         I need a static time slicer which is based on fixed text slecetors as i mentioned in post. The way you tried to solve the issue seems good but fails to counter the month no. issue. Since you are evaluating your calcuation over a numeric column that has 6,12,18 as values.The moment i use "values" function it will take up the distinct values of the month that rnges from 1-12 only. Since i have a date column over which i will be filtering the sales value. 

what you say??

Regards,

Hemant   

[iamprajot]

That's not difficult to achive with the logic provided, I could manipulate the DAX provided with this one.

Total Cost based on Time Period =
CALCULATE(SUM(TableName[Sales]),

FILTER(ALL(TableName[Date].[MonthNo]),

TableName[Date].[MonthNo] <= [Selected])))

also, change previous table values with 3,6,9,12,18,24 (as months number)

and if u have more than 1 Year data than create s slicer for that too otherwise this solution adds first 3 Months Sales from each Year.