Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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.
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.
@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 )
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |