Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jmarcrum
Helper II
Helper II

Dynamic Percent Change - Hour to Hour (row to row) - working with slicers

Hi all,

 

I feel like this is really simple and I'm just overthinking it.  I have a dataset with a datetime value representing each hour of the day, an item number field and a numerical load value.  The load value may/may-not change per item each hour.  If it changes (per item), I want to show the percent change and if not, show 0% change. 

 

I want to be able to show this on a line chart by the way.  But the typical hierarchy for timestamp only goes down to days... if you know how to show that, any suggestions would be helpful as well!

 

The catch is.... I want to be able to use this calculation with a slicer... so pick a month or day, and have the percent change update (for each item).  Can this be done per item and per slicer selection?  Example below:

 

timestamp                          item                load         percent_change

1/1/2021 12:00:00 AM        T13377           10            

1/1/2021 01:00:00 AM        T13377           5               -50%

1/1/2021 02:00:00 AM        T13377           10             100%

1/1/2021 03:00:00 AM        T13377           9.7             -3%

1/1/2021 12:00:00 AM        T13378           10                   

1/1/2021 12:00:00 AM        T13379           5

1/1/2021 01:00:00 AM        T13378           10               0%

 

etc.

 

Can anyone help me with this?  I attached a sample dataset. 

https://www.dropbox.com/s/a7rg2z17d7cmx4h/PBI%20Forum%20Data.csv?dl=0

 

Joseph

1 ACCEPTED SOLUTION
jmarcrum
Helper II
Helper II

Thank you both!  I actually took a little bit from both of your solutions to obtain the measure I needed... all in one measure.  See below.

 

ROC =
VAR item = MAX(Table[item])
VAR ts = MAX(Table[timestamp])
VAR load = MAX(Table[load])

VAR prev_ts = CALCULATE(MAX(Table[timestamp]), Table[timestamp] < ts)
VAR prev_load = CALCULATE(MAX(Table[load]), Table[timestamp] = prev_ts && Table[item] = item)

VAR change = load - prev_load
VAR pct = DIVIDE(change, prev_load, 0)

RETURN IF(prev_load = 0, 0, pct)

 

View solution in original post

3 REPLIES 3
jmarcrum
Helper II
Helper II

Thank you both!  I actually took a little bit from both of your solutions to obtain the measure I needed... all in one measure.  See below.

 

ROC =
VAR item = MAX(Table[item])
VAR ts = MAX(Table[timestamp])
VAR load = MAX(Table[load])

VAR prev_ts = CALCULATE(MAX(Table[timestamp]), Table[timestamp] < ts)
VAR prev_load = CALCULATE(MAX(Table[load]), Table[timestamp] = prev_ts && Table[item] = item)

VAR change = load - prev_load
VAR pct = DIVIDE(change, prev_load, 0)

RETURN IF(prev_load = 0, 0, pct)

 

Jihwan_Kim
Super User
Super User

Hi, @jmarcrum 

I am not sure if I correctly downloaded your sample dataset, but I only see the same number for T13377 on 1st Jan, for instance. And there are no changes. Other items also show the same pattern and on the other days as well.

Anyway, my approach is to have dim-Date-Table and dim-Time-Table, and write the measure to calculate the previous load.

 

Previous Load =
VAR currenttimestamp =
MAX ( TimeTable[Hour] )
VAR previoustimestamp =
CALCULATE ( MAX ( 'TimeTable'[Hour] ), TimeTable[Hour] < currenttimestamp )
VAR previousload =
CALCULATE ( [load], TimeTable[Hour] = previoustimestamp )
RETURN
previousload
 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


amitchandak
Super User
Super User

@jmarcrum , Create two measures like this and try

 

timelast =
VAR __id = MAX ('Table'[item] )
VAR __date = CALCULATE ( MAX('Table'[timestamp] ), ALLSELECTED ('Table' ), 'Table'[item] = __id )
CALCULATE ( max ('Table'[timestamp] ), VALUES ('Table'[item] ),'Table'[item] = __id,'Table'[timestamp] < __date )

 

 

Measure 2=
VAR __id = MAX ('Table'[item] )
VAR __date = timelast
Sum ('Table'[load] )- CALCULATE ( Sum ('Table'[load] ), VALUES ('Table'[item] ),'Table'[item] = __id,'Table'[timestamp] = __date )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.