cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moltra
Helper IV
Helper IV

Formula to calculate a goal that changes over a period in a Polynomial.

I am trying to make a formula that changes a calculated goal measure so that it matches the Polynomial results of the measure being monitored.  Ignore the scale on the left, the calculated goal measure goes from the Left to the Right.  The below chart shows rate of change of the calculated goal measure.  

 

poly.jpg

 

Here is the calculated goal measure that I am using.

TimePeriodMax is CurrentPeriod Max divided by Numbers of Period slices in measurement Period.

PeriodToNow is the number of Period slices in the measurement Period to the current period slice.

DIVIDE ( ( TimePeriodMax * PeriodToNow ), CurrentPeriodmax ))* correctionFactor  Returns a percentage value.  

The Value is 0 at the beginning of the Period and 100% at the end of the period.

4 REPLIES 4
moltra
Helper IV
Helper IV

I ended up just using the DAX SWITCH function to change a correction factor to estimate what the goal value should be.  I am adjusting the SWITCH function values as needed to tweak the goal value.   I will post some images today that shows with and without the correction factor, so everyone can understand what I am trying to do.

 

 

 

DayKwhGoalPct = //(Divide(([CurrentDayHourlykWhMax] * [TimeToNow]),[CurrentDayKwhMax]))*.175
VAR KwhTimePeriodMax = [CurrentDayHourlykWhMax] //this is the kwh max for the total period being monitored divided by the Hours/days in the period.
VAR PeriodToNow = [TimeToNow] //This is the Hours/days in the period to now
VAR CurrentPeriodmax = [CurrentDayKwhMax] //the maximum kwh for the day/month/year beeing monitored
//start to 10 => .175, 10:30 to 11 =>.39, 11:00 to 12:30 =>.6, 12:30 to 13:00 =>.75, 13:00 to 14:00 =>.9, 14:00 to :00 =>1.0
VAR CorrectionFactor =
    SWITCH (
        TRUE,
        PeriodToNow <= 2, .175,
        PeriodToNow <= 4, .4,
        PeriodToNow <= 4.5, .5,
        PeriodToNow <= 5.5, .6,
        PeriodToNow <= 6.5, .8,
        PeriodToNow <= 7.5, .9,
        PeriodToNow <= 8, 1,
        1
    )
VAR FinalResult =
    ( DIVIDE ( ( KwhTimePeriodMax * PeriodToNow ), CurrentPeriodmax ) ) * correctionFactor
RETURN
    { FinalResult }

 

 

In the below graphic, you can see the differences in the goal, the outer color in the visuals.  If you look at the lower visual it looks like you are missing your goal by a long amount, but since the amount of sunlight is only a portion (40%) of what it will be at the peak.  So the top visual has a more accurage goal, it uses a correctionfactor of 0.4. 

moltra_0-1627310314076.png

 

v-xiaotang
Community Support
Community Support

Hi @moltra 

I didn't fully understand what you mean. you want the Value of the blue Measure is 0 at the beginning of the Period and 100% at the end of the period, right?

and I'm not very familiar to your model, so what's the logic of calculating a goal that changes over a period in a Polynomial?

 

Any question, please let me know. looking forward to receiving your reply.

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

I am trying to graph the output of a solar plant.  The output of it changes depending on the time of day.  Its max output is in the middle of the day.

v-xiaotang
Community Support
Community Support

Hi @moltra 

still not very clear, I find a tutorial, please take a look at it,

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...

hope it helps! 

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors