Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Show data on line chart based on Period

Hi all,

 

I have a table contain Time  column (drill down by hour) and count column.

 
 

The data I try to show in line chart have a slicer called period (Hourly, Daily, Monthly, Weekly, Yearly)

 

 

Can I choose the value on Period and the chart will reflect the period?

Any help is appreciated.

 

Thank you.

 

Dashboards
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    6 years ago

    Hi Anonymous, 

     

    You can create a measure below, then drag this measure to the line chart: 

    Measure = 
var y= CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date])))) 
var q= CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&QUARTER('Table'[Range Date])=QUARTER(MAX('Table'[Range Date]))))
var m=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&MONTH('Table'[Range Date])=MONTH(MAX('Table'[Range Date]))))
var w=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&WEEKNUM('Table'[Range Date])=WEEKNUM(MAX('Table'[Range Date]))))
var h=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&MONTH('Table'[Range Date])=MONTH(MAX('Table'[Range Date]))&& DAY('Table'[Range Date])=DAY(MAX('Table'[Range Date]))))
var d=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&MONTH('Table'[Range Date])=MONTH(MAX('Table'[Range Date]))&& DAY('Table'[Range Date])=DAY(MAX('Table'[Range Date]))&&HOUR('Table'[Range Date])=HOUR(MAX('Table'[Range Date]))))
return SWITCH(SELECTEDVALUE('Table (2)'[Period]),"Monthly",m,"Weekly",w,"Yearly",y,"Hourly",h,"Quarterly",q,"Daily",d)

     

     

    Best Regards,
    Qiuyun Yu

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    6 years ago

    Hi Anonymous, 

     

    You can create a measure below, then drag this measure to the line chart: 

    Measure = 
var y= CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date])))) 
var q= CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&QUARTER('Table'[Range Date])=QUARTER(MAX('Table'[Range Date]))))
var m=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&MONTH('Table'[Range Date])=MONTH(MAX('Table'[Range Date]))))
var w=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&WEEKNUM('Table'[Range Date])=WEEKNUM(MAX('Table'[Range Date]))))
var h=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&MONTH('Table'[Range Date])=MONTH(MAX('Table'[Range Date]))&& DAY('Table'[Range Date])=DAY(MAX('Table'[Range Date]))))
var d=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),YEAR('Table'[Range Date])=YEAR(MAX('Table'[Range Date]))&&MONTH('Table'[Range Date])=MONTH(MAX('Table'[Range Date]))&& DAY('Table'[Range Date])=DAY(MAX('Table'[Range Date]))&&HOUR('Table'[Range Date])=HOUR(MAX('Table'[Range Date]))))
return SWITCH(SELECTEDVALUE('Table (2)'[Period]),"Monthly",m,"Weekly",w,"Yearly",y,"Hourly",h,"Quarterly",q,"Daily",d)

     

     

    Best Regards,
    Qiuyun Yu