Forum Discussion

FOXYBARK's avatar
FOXYBARK
Helper III
4 years ago
Solved

Percent change using hours

HI. 

I need help  perfoming a percent change using hours in a day. This will ultimately go in a line graph. Below is a snip of my dataset and commands. It's the previous value I can't get to work. I only want the distinct count of access numbers for the previous hour so I plot on a line graph. 

 

 

 

 

% Change = VAR CurrentValue = [cntAccessNumbers]
VAR PreviousValue = CALCULATE([cntAccessNumbers], Bdge1[BADGE_SCAN_TS]-1)
RETURN
DIVIDE(CurrentValue -  PreviousValue,
PreviousValue)

 

 Any advice is appreciated. 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  FOXYBARK ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    Measure_cntAccessNumbers = MAX('Table'[cntAccessNumbers])
    Measure =
var _cvurrent=
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[BADGE_SCAN_TS])=YEAR(MAX('Table'[BADGE_SCAN_TS]))&&
    MONTH('Table'[BADGE_SCAN_TS])=MONTH(MAX('Table'[BADGE_SCAN_TS]))&&
    DAY('Table'[BADGE_SCAN_TS])=DAY(MAX('Table'[BADGE_SCAN_TS]))&&
    HOUR('Table'[BADGE_SCAN_TS])=HOUR(MAX('Table'[BADGE_SCAN_TS]))),[Measure_cntAccessNumbers])
var _PreviousValue=
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[BADGE_SCAN_TS])=YEAR(MAX('Table'[BADGE_SCAN_TS]))&&
    MONTH('Table'[BADGE_SCAN_TS])=MONTH(MAX('Table'[BADGE_SCAN_TS]))&&
    DAY('Table'[BADGE_SCAN_TS])=DAY(MAX('Table'[BADGE_SCAN_TS]))&&
    HOUR('Table'[BADGE_SCAN_TS])=HOUR(MAX('Table'[BADGE_SCAN_TS]))-1),[Measure_cntAccessNumbers])
return
DIVIDE(
    _cvurrent - _PreviousValue ,
    _PreviousValue)

    2. Result:

    There is one more thing to note: if your results are all 0, it may be that you have not set the % format, click your Measure -- Measure tools -- %

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    FOXYBARK , Create an hour table and join it with the hour(Number format) of your table

     

    then you can have measures like

     

    This hour= CALCULATE(sum('Table'[Qty]), FILTER(ALL('hour'),'Date'[hour]=max('Date'[hour])))
    Last hour= CALCULATE(sum('Table'[Qty]), FILTER(ALL('hour'),'Date'[hour]=max('Date'[hour])-1))

    • FOXYBARK's avatar
      FOXYBARK
      Helper III
      4 years ago

      Would my hour table only have the hour or should it have other columns as well? Such as date?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  FOXYBARK ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    Measure_cntAccessNumbers = MAX('Table'[cntAccessNumbers])
    Measure =
var _cvurrent=
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[BADGE_SCAN_TS])=YEAR(MAX('Table'[BADGE_SCAN_TS]))&&
    MONTH('Table'[BADGE_SCAN_TS])=MONTH(MAX('Table'[BADGE_SCAN_TS]))&&
    DAY('Table'[BADGE_SCAN_TS])=DAY(MAX('Table'[BADGE_SCAN_TS]))&&
    HOUR('Table'[BADGE_SCAN_TS])=HOUR(MAX('Table'[BADGE_SCAN_TS]))),[Measure_cntAccessNumbers])
var _PreviousValue=
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[BADGE_SCAN_TS])=YEAR(MAX('Table'[BADGE_SCAN_TS]))&&
    MONTH('Table'[BADGE_SCAN_TS])=MONTH(MAX('Table'[BADGE_SCAN_TS]))&&
    DAY('Table'[BADGE_SCAN_TS])=DAY(MAX('Table'[BADGE_SCAN_TS]))&&
    HOUR('Table'[BADGE_SCAN_TS])=HOUR(MAX('Table'[BADGE_SCAN_TS]))-1),[Measure_cntAccessNumbers])
return
DIVIDE(
    _cvurrent - _PreviousValue ,
    _PreviousValue)

    2. Result:

    There is one more thing to note: if your results are all 0, it may be that you have not set the % format, click your Measure -- Measure tools -- %

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly