Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Prediction Line

I need to create a prediction line for how the progress bar should look like.
(I am using a line and stacked column chart)

In my data table I have begin dates and end dates. I need a measure to choose the earliest and latest dates and create a linear line between it.
The yellow line is a new column, but it gives back the correct line in overall view. But as I use slicer or filter it doesn't change (as expected). Here is the code for it:
Regression =
VAR mind = MIN(Data_Table[Begin Date])
VAR maxd = MAX(Data_Table[End Date])
VAR dat = 'Calendar'[Date]
VAR dated = DATEDIFF(mind,maxd,DAY)
VAR todated = DATEDIFF(dat,maxd,DAY)
VAR fromdated = DATEDIFF(mind,dat,DAY)
RETURN IF(AND(fromdated>=0,todated>=0),(DIVIDE(todated,dated)-1)*(-1),IF(fromdated<0,0,1))

After this I tried to create the measure for it, but it doesn't work like it.
Here is the code:
Regression 2 =
VAR mind = MIN(Data_Table[Begin Date])
VAR maxd = MAX(Data_Table[End Date])
VAR dat = SELECTEDVALUE('Calendar'[Date])
VAR dated = DATEDIFF(mind,maxd,DAY)
VAR todated = DATEDIFF(dat,maxd,DAY)
VAR fromdated = DATEDIFF(mind,dat,DAY)
RETURN IF(AND(fromdated>=0,todated>=0),(DIVIDE(todated,dated)-1)*(-1),IF(fromdated<0,0,IF(todated<0,1,0)))

I don't mind the spikes on the dates where there is no date (missing column), but the slope start later and before that the value is 1 rather 0.

Any help with other approaches as well is much appricated it and have a lovely day.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hey
      Unfortunately this is not an option as I don't see it.

       

  • some_bih's avatar
    some_bih
    Community Champion

    Hi Anonymous please check that you have the latest version of Power BI. For forecast feature please note

    The forecasting feature is only available for line chart visuals.