Calculate data from absolute measures

[isidnis]

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.

[AlB]

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

[isidnis]

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)

[AlB]

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_
)

[isidnis]

Sorry about the lack of info I am pretty new in all this area:)

I am going to try to be more specific. The table I have has actually 3 columns deviceid:that identifies the utility counter, timestamp of the measure and absolute measure of the counter that is a number that goes up everytime a user consumes electricity.

What I want to achieve, (your last formula didn´t work) is something like:

I was thinking about having a column with differential values of each day, 

DAY CONDUMPTION = MAX(Table[Measure])-(CALCULATE(MAX(Table[Measure]),PREVIOUSDAY(Table[DayColumn]))

But it doesn´t work either.

Sorry again for the misunderstanding

[Anonymous]

You should create a fact table that will hold each day for each deviceid and you should linearly *interpolate* the consumption for each day. Then you'll be able to EASILY calculate consumption for any period of time.

If you don't do it the way I'm saying, you'll be struggling with DAX making it more complex and slower than it should be. In other words, the granularity of your fact should be the day, not days with gaps.

You can create the fact table using Power Query (M). It should not be difficult. Remember: the key to a simple and correct model is LINEAR INTERPOLATION.

Best
D.

[isidnis]

Thanks for your help. Probably out of my skill set to do linear interpolation on power query. I have been able to get a table with only one measure per day but not sure how to get a column that has the incremental value on power query. I have been able to do it with a measure.

[Anonymous]

The way to do it in PQ is this. Let's say you have a day a with a read of A and day b with a read of B (A<B) and there are no other days between a and b. Hence, there are n=(b-a) days altogether you're looking at (we assume that the day on which a read is taken belongs to the right interval: [a0, a1), [a2, a3)...). So you have to now create n, a=a0, a1,...,a(n-1)=b-1, days and allocate to them in a linear fashion the consumption of B-A. Each day will have a consumption of (B-A)/n and this is the linear interpolation I was talking about. Now, this procedure must be carried out for each and every pair of consecutive days.

Best
D.