Forum Discussion
Calculate data from absolute measures
Hello,
I cannot find a solution for a probably very simple question. I have a table with an absolute measure of a utility counter with the timestamp when it was measured e.g
The key is that the measured value always goes up. What I would like to do is to find out information about the consumption eg how much has been consumed this month, what is the difference between months, average consumption per day etc. The timestamps are quite random and some might be missing.
7 Replies
Hi isidnis
Probably the easiest is to create an additional calculated column with the day only (i.e., discarding the time) :
DayColumn = INT(Table1[Timestamp])
and make the new column type date. Then create a standard date table and you'll be able to set up many of the things you're describing easily. For instance, average consumption per day:
1. Set Table1[DayColumn] in the rows of a matrix visual
2. Create a measure and place it on the matrix:
AverageMeasure = AVERAGE(Table1[Measure])
Thanks a lot for your answer, my problem is that the measure is an absolute value of a counter. So the average consumption per day would be (Value at the end of the day 1 - Value at the beginning of the day 1) +(Value at the end of the day 2 - Value at the beginning of the day 2)...... / (Number of days with data)
It would help and save us time if you described in detail what you need from the beginning, like you've done now. You can then create a measure and place it in a card visual (still using the new calculated column described earlier):
Measure2 = AVERAGEX ( DISTINCT ( Table1[DayColumn] ), VAR ValBeginDay_ = CALCULATE ( MIN ( Table1[Measure] ) ) VAR ValEndDay_ = CALCULATE ( MAX ( Table1[Measure] ) ) RETURN ValEndDay_ - ValBeginDay_ )
