Percent change using hours

[FOXYBARK]

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

[v-yangliu-msft · Accepted solution]

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

[amitchandak]

@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]

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