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
Community 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