Forum Discussion
Calculate data from absolute measures
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)
Community Champion
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_
)
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
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.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.