Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tijn
Frequent Visitor

How to smooth or divide values over time

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)

Tijn_0-1661436414032.png

 

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
 
1 ACCEPTED SOLUTION

6 REPLIES 6
Tijn
Frequent Visitor

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. 

Would you be ok with a calculated column?

Tijn
Frequent Visitor

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 !

see attached

 

lbendlin
Super User
Super User

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.

 

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)
 

lbendlin_0-1661742107083.png

 

 

 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors