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 1...
General Comment
Need Help
Tutorial Requests
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    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.

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

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's avatar
Noak
Helper IV
9 years ago

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's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    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's avatar
      Noak
      Helper IV
      9 years ago

      Hi tringuyenminh92,

      Please explain the content of value column?

      • tringuyenminh92's avatar
        tringuyenminh92
        Memorable Member
        9 years ago

        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.