Forum Discussion

Noak's avatar
Noak
Helper IV
9 years ago
Solved

Time selector

Hi,

Im generating cloud report using SQL server.

I need to to let the viewer use 3 different selectors:

1.daily - shows last 24h

2.weekly - last 7 days

3.monthly - last 4 weeks

4.yearly- last 12 months

 

please help me how can i creat 3 different time selector based on 1 datetime column?

 

  • Hi Noak,

     

    Cause you need 4 selections so I create filter table by "Enter data" option as Period table in picture

     

    Create new Measure with swtich options based on user's selection:

     

     

    Calcualted Measure - Value = if(HASONEVALUE('Filters'[Period]),
    	SWITCH(FIRSTNONBLANK('Filters'[Period],'Filters'[Period]),
    	"Last 24h",CALCULATE(sum(SampleData[Value]),FILTER(SampleData,SampleData[Date] >= NOW()-1  )),
    	"Last Week",CALCULATE(sum(SampleData[Value]),FILTER(SampleData,SampleData[Date] >= NOW()-7  )),
    	"Last Month",CALCULATE(sum(SampleData[Value]),FILTER(SampleData,SampleData[Date] >= NOW()-35  )),
    	"Last Year",CALCULATE(sum(SampleData[Value]),FILTER(SampleData,SampleData[Date] >= NOW()-365  )),
    		BLANK()
    	) )

    (Not sure meaning of 13 months, so i let it minus 365 days, you could modify it)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    In case you want to change the scenario with YTD, MTD, today, this week, this month, this year. You could use equal express with Today() or Month(Today()), Weeknum(Today()), Year(today())

     

     

    If this works for you please accept it as solution and also give KUDOS.

16 Replies

  • Please refer to THIS blog post by Rob Collie. Using the Switch function to create the dynamic slicers.

    • Noak's avatar
      Noak
      Helper IV

      Still have problem implementing the switch function..Im not a pro (new in the enviroment)

      please help.

      BhaveshPatel

      • tringuyenminh92's avatar
        tringuyenminh92
        Memorable Member

        Could you please upload sample data and your expectation as picture? So I could quickly help you with sample file.