Time selector

[Noak]

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?

[BhaveshPatel]

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

[Noak]

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

please help.

@BhaveshPatel

[tringuyenminh92]

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

[Noak]

My data is from SQL Server I dont have sample file , I can creat somthing by myself.

but basicly I need dynamic selector .

---

@tringuyenminh92 wrote:

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

---

@tringuyenminh92

[Noak]

Thank you @tringuyenminh92 , you might have saved my day.

Basicly the end client see lot of data with interval of 5 minutes:

I want the most "elegant" way to present the data for 4 possabilities:

1. last 24h

2.last week

3.last month

4.last year

P.S I noticed its not possible to drill down to minutes.

[tringuyenminh92]

Hi @Noak,

I would like to confirm about definition of 

Last 24h: is that  from NOW - 24H or today ?

Last Week: is that from now - 7 days or from first date of week to now?

Last Month is that from first date of month to now or something else?

Last Month is that from first date of year to now or something else?

[Noak]

Hi @tringuyenminh92,

via below:

---

@tringuyenminh92 wrote:

Hi @Noak,

I would like to confirm about definition of 

Last 24h: is that  from NOW - 24H from now

Last Week: is that from now - 7 days from now (if now its saturday 12:00AM then I need to present data from last saturday 12:00AM )

Last Month is that from first date of month to now or something else? 35d from now

Last Month is that from first date of year to now or something else?  A year will be considered as 13 months

---

Again thank you!

[tringuyenminh92 · Accepted solution]

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.

[Noak]

Hi @tringuyenminh92,

Please explain the content of value column?

[tringuyenminh92]

It's your value field. You have 5 measures value in chart in the picture, don't you? or if you are counting number of countries, you could replace the sum(sample[value]) by count(SheetName[FieldColumn])] to fit your case.

[Noak]

Hi Again @tringuyenminh92,

Its not working (please via attached image)  =\.

[tringuyenminh92]

Hi @Noak, 

 Country is just example meaning.

Did you drag and drop  "Calculated Measure - Value" to the Value field in line chart? Coud you click to line chart and show me the Fields in there? 

[Noak]

Sure @tringuyenminh92

[tringuyenminh92]

Hi @Noak,

So you need replace measure: "Average of sum..." by "Calcualted Measure - value" and try the filter selections

[Noak]

Thank yoy @tringuyenminh92 you helped me allot!

[Noak]

Hi @tringuyenminh92,

still I dont understand what you mean, My chart dont focuse on country:

let me explaing:

the chrt present:

1.Date= every 5 minutes date +time

2.MSG type(7 different types)

3.Provider(5 providers)

4.Site(5 sites)

My bar chart presentS: avg MSG count by date & MSG type

In order to present the data respectivly I need dynamic selector.

now the only missing part for me is the value column , please help.