Forum Discussion

aj1107's avatar
aj1107
Advocate I
9 years ago
Solved

Last N month based slicer selection

I need to show the prior 3-month data count when selecting a date in the slicer. Created date table and related with txn table.   I can get the expected result if pass the slicer selected value as ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi aj1107,

     

    You can refer to below steps to build a last N month slicer.

     

    1. Add column to convert dt to date.

    Date = DATEVALUE([dt]&"/2017")

     

     

    2. Use above date column to create a slicer table.

    Selector Table2 = ADDCOLUMNS(VALUES(Test[Date]),"Month",FORMAT([Date],"mmmm"))

     

     

    3. Add measure to get selected date.

    Select = IF(HASONEVALUE('Selector Table2'[Date]),VALUES('Selector Table2'[Date]),BLANK())

     

    4. Write measure to count id and and filter records.

    Count ID = IF(MAX([Date])<[select],COUNT(Test[id]),BLANK())

     

    5. Create visuals.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi aj1107,

 

You can refer to below steps to build a last N month slicer.

 

1. Add column to convert dt to date.

Date = DATEVALUE([dt]&"/2017")

 

 

2. Use above date column to create a slicer table.

Selector Table2 = ADDCOLUMNS(VALUES(Test[Date]),"Month",FORMAT([Date],"mmmm"))

 

 

3. Add measure to get selected date.

Select = IF(HASONEVALUE('Selector Table2'[Date]),VALUES('Selector Table2'[Date]),BLANK())

 

4. Write measure to count id and and filter records.

Count ID = IF(MAX([Date])<[select],COUNT(Test[id]),BLANK())

 

5. Create visuals.

 

Regards,

Xiaoxin Sheng

aj1107's avatar
aj1107
Advocate I
9 years ago

Thanks for sharing the steps.

 

Quick question, I already have a date dimension related to test table. Per the given steps I need to create another date slicer table without a relationship. 

 

existing date slicer is referred to multiple charts within a page. now i need to have another slicer for the last N month chart.

 

Is that possible to use the existing date dimension (Related->test) without another slicer table. By selecting any single date evaluate using dax while retaining the relationship.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi aj1107,

     

    I think you can use the current calendar table as the source of slicer, but it will count through all similar month records, so I think you should add a year filter.

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi ,
      I have a pretty much similar issue where i need to show last 6 months data based on the selection of the slicer which is basically a month slicer(Jan,Feb) .

      In my model i do have one date dimension on which multiple measures has been calculated so i can't have other date dimension 

       

      We were able to achieve it by providing a slicer of date which is basically coming from a different table which has been created apart from the date dimensiion.

      Can you please guide me through what could be necessary steps to be taken to achieve this scenerio ?

      Any help would be really appreciated. 

      Regards
      Prabin Nepak