How to smooth or divide values over time

[Tijn]

Sample xslx-file in this DropBox link:https://www.dropbox.com/s/ktjbjszsnromv76/Usages%20sample%20data.xlsx?dl=0

Dear all :),

I am currently working on an energy usage dashboard. I have a dataset wich gives me usages for a certain day for different units. The problem is that sometimes there are a few days which did not report the usage. In such cases, the first upfollowing usage value will be much higher. This since it includes the usage of that specific day, but also the usages of the previous days which were not reported. This makes my usage graph look like (current) while I would like it to be like (desired). (Only based on unit A1)

At (desired) the usage of upfollowing missing days is calculated by evenly distributing the usage of the next known day. Example; the usage on 10/01/2022 is 9.6 while the usages of 08/01/2022 and 09/01/2022 are missing. In (desired) this means that the reported usage on the 8th, 9th and 10th is 3.2 (9.6/3). 

I already managed to create an extra table which includes all possible dates that I need. Here I created a calculated column which calculates the total usage across all units using the following formula:

var divided_usage = Calculate(

    SUM(Sample[average_usage]),

    FILTER(Sample,

    Sample[lastdate] < 'CALENDAR TABLE'[Date] &&

    Sample[nextdate] > 'CALENDAR TABLE'[Date]))

return divided_usage

When looking at the column that is now created, it seems to do the job. However, this data needs also to be visualized per unit using value selection, filtering and/or slicers. Besides, I would like to visualise this data in a matrix per unit. When I do this, the missing dates will remain blank/NULL. I already tried creating columns without using the calculate function and I tried working with measures. Nothing really does the trick unfortunately. There are cases the linegraph seems to work out, but then the matrix does not (the blanks remain). And if you then select a unit in the matrix, the linegraph responds but doesn't change to the values of the specific unit. (In practice the dataset is huge, thus creating a new calculated table with all dates for each unit and the corresponding usages is performance wise not desired.)

Thanks in advance for helping out, finding a solution would be really great! If there are any questions, let me hear.

Cheers, Tijn

[Tijn]

Great, thanks @lbendlin !

About the totals, I would indeed like to have a graph with all the usages combined. In which table do I place this column and what would the code then look like? I'm struggling with this part because the unit condition should disappear, which should also change how dax calculates the total usage. 

[lbendlin]

Would you be ok with a calculated column?

[Tijn]

I would ofcourse prefer a measure in this case, but if a calculated column does the job and is way easier then that would be fine as well. Thanks @lbendlin !

[lbendlin · Accepted solution]

see attached

[lbendlin]

I already managed to create an extra table which includes all possible dates that I need. 

This is a good (and required) first step.  Next step is to write a measure for each of these dates that either takes the reading for that day,  or calculates the appropriate fraction of the next reading for days without reading. If you only ever miss one then you can take half of the value , otherwise you'll first need to figure ot how many readings were missed etc.  For days with reading but with prior days missing you need to deduct accordingly.

[lbendlin]

Calculated Table:

Combined = CROSSJOIN(values('Sample'[Unit]),'Calendar')

Measure:

Measure =

var d = selectedvalue('Combined'[Date])

var u = SELECTEDVALUE(Combined[Unit])

var us = CALCULATE(sum('Sample'[Usage]),'Sample'[Date]=d,'Sample'[Unit]=u)

var mxd = CALCULATE(min('Sample'[Date]),'Sample'[Date]>d,'Sample'[Unit]=u)

var mxdv = CALCULATE(sum('Sample'[Usage]),'Sample'[Date]=mxd,'Sample'[Unit]=u)

var mnd = CALCULATE(max('Sample'[Date]),'Sample'[Date]<d,'Sample'[Unit]=u)

return switch(true(),

isblank(us) ,divide(mxdv,DATEDIFF(mnd,mxd,DAY)),

datediff(mnd,d,DAY)>1,divide(us,DATEDIFF(mnd,d,DAY)),

us)

As you can see the totals are missing. This is due to the design of the measure.  An easy solution would be to use a calculated column instead.