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