Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Automatic Slicer

Hello,   I have a report which adds in a months data through SQL agent at the end of each month.   This populates the slicer like in the image.   As currently, I have to get the report from the...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Managed to get around this by putting a case statement into my SQL view that would show the previous months data.

     

    case when LEFT(CONVERT(varchar,DATEADD(MONTH,-1,GETDATE()),112),6) = SnapshotPeriod then 1 else 0 end as IsCurrentMth.

     

    I then merged the 1 from IsCurrentMth with the standard SnapshotPeriod to get my new slicer.

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi , Anonymous 

According to your current description ,you can try to create a calculated table and create a column like followings:

Table 2 = CALENDAR(DATE(2019,07,01),TODAY())
Column 2 = IF(FORMAT([Date],"YYYY MM")=FORMAT(TODAY(),"YYYY MM"),"Current Month",FORMAT([Date],"YYYY MM"))

Here is  sample :

 

(This month is January, then in the next month, “2020 1” will be added in the slicer, and the current month will point to ”2020 02”)

Url: https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EcJuwmZtQkdJuLxTDHiQL_ABdcfl83k2ZmHELE1k04hoqQ?e=meMYZg

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    Thanks for the reply. Could I ask which part tells the slicer to default to stay on current month?

     

    Thanks in advance

     

    Liam

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    v-easonf-msft 

     

    Thanks for the reply

     

    Could I ask which part tells the slicer to default to stay on current month?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-easonf-msft ,

     

    If the slicer date E.G. 201912 was coming from a column in a SQL view, would there be an alternative way to make it default to current month in the slicer?

     

    Thanks

     

    Liam

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Managed to get around this by putting a case statement into my SQL view that would show the previous months data.

       

      case when LEFT(CONVERT(varchar,DATEADD(MONTH,-1,GETDATE()),112),6) = SnapshotPeriod then 1 else 0 end as IsCurrentMth.

       

      I then merged the 1 from IsCurrentMth with the standard SnapshotPeriod to get my new slicer.