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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors