DAX Forecast using minus rather than SM Running Total

[amitche3]

Hi

I am trying to calculate a Forecast  by taking away a "Reomoval value"     in DAX

Index is every 7 interval

Forecast only startd after the last CRT value then  subtracting  the Removal value from previous week as per the Forecast column example

My issue is I cant get the value in the row above in the Forecast column when I am calculating it 

This is my calculated column attempt  but   the bold row needs to refer to the row above in the Forecast column which isnt allowed as circular reference

Do I need a VAR to store the Forecast from row above but cant seem to work out the placement.

Forecast = IF( 'CRT Table'[From Date] >
CALCULATE( LASTDATE ( 'CRT Table'[From Date] ),FILTER('CRT Table', 'CRT Table'[CRT] >0)) ,

CALCULATE(MAX('CRT Table'[CRT]),FILTER('CRT Table','CRT Table'[Index] =EARLIER('CRT Table'[Index] )-7))
-
CALCULATE(MAX('CRT Table'[Removal Count]),FILTER('CRT Table','CRT Table'[Index] =EARLIER('CRT Table'[Index] )-7))
,
'CRT Table'[CRT]
)

Index	Removal Count	CRT	Forecast
1464		69	69
1471		72	72
1478		69	69
1485		70	70
1492		71	71
1499	2	77	77
1506	7	81	81
1513	13		74
1520	9		61
1527	2		52
1534	3		50
1541			47
1548	2		47
1555	1		45
1562	4		44
1569	2		40
1576	5		38
1583	1		33
1590	5		32
1597			27
1604			27
1611	1		27

[edhans]

Hi @amitche3 ,

I strongly recommend you avoid calculated columns. See below for links to reasons. But I will give you the necessary formula.

Instead, use a measure:

Previous Index = 
VAR varIndex =
    SELECTEDVALUE( Data[Index] )
RETURN
    CALCULATE(
        MAX( Data[CRT] ),
        data[Index] = varIndex - 7
    )

If you must use a calculated column because you are using this in a slicer or something, then you can use this formula:

Previous Index Column = 
VAR varIndex = Data[Index]
RETURN
    MAXX(
        FILTER(
            Data,
            Data[Index] = varIndex - 7
        ),
        Data[CRT]
    )

But measures will generally perform better. The time to use calculated columns isn't never, but it is rarely. 😁

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns

[amitche3]

Hi edhans

Thanks for your reply

It is the Forecast column which I need help with as I need it to count down

I have creating a calculated column and the values were OK but not able to refer to this column in the Forecast calculation as circular reference.

I created a measure but no valueis being returned when I use it

Forecast = IF( 'CRT Weekly Forecast'[From Date] >

CALCULATE( LASTDATE ( 'CRT Weekly Forecast'[From Date] ),FILTER('CRT Weekly Forecast', 'CRT Weekly Forecast'[CRT] >0)) ,

[Previous CRT] -

CALCULATE(MAX('CRT Weekly Forecast'[Planned Removal Count]),FILTER('CRT Weekly Forecast','CRT Weekly Forecast'[Index] =EARLIER('CRT Weekly Forecast'[Index] )-7))

,

'CRT Weekly Forecast'[CRT]

)

The Forecast Result should be as per my 1st post but I am getting

Period Week      Planned Removal Count CRT        Forecast              Index

2021_22 P01 Wk01                       69           69           1464

2021_22 P01 Wk02                       72           72           1471

2021_22 P01 Wk03                       69           69           1478

2021_22 P01 Wk04                       70           70           1485

2021_22 P02 Wk01                       71           71           1492

2021_22 P02 Wk02         2             77           77           1499

2021_22 P02 Wk03         7             81           81           1506

2021_22 P02 Wk04         13                         -7           1513

2021_22 P03 Wk01         9                           -13         1520

2021_22 P03 Wk02         2                           -9           1527

2021_22 P03 Wk03         3                           -2           1534

2021_22 P03 Wk04                                      -3           1541

[edhans]

Did you try the formula I gave for the calcualted column? The code is shown above, this is how it looks in the desktop. You are getting a circular reference because you are using CALCULATE, and that has to do with context transition. There is no need for CALCULATE here.

And there is never a need to use EARLIER or EARLIEST. Those were replaced in 2015/2016 with variables. The only reason you should know of EARLIER/EARLIEST is when you see old code you will know what it is and how to replace it with variables.

My code simply returns the forecast for the previous index row. You can then do whatever math you want with the CRT column. Just add -[CRT] will work.

If that isn't what you need, then provide a screenshot of how you expect the data to be shown using Excel, with an explanation of the math. And if posting any dax, please format it using daxformatter.com instead of long runon formulas like Excel creates. It is hard to read those long lines. 😊

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[amitche3]

Hi edhans

I am trying to calculate the Forecast column  or a measure.

If you look at the top of the post, the Forecast column is the expected result I am trying to achieve

Logic is

After the last CRT row value , (in this case Index 1608) start subtracting the Removal Count as a Running Decreasing Total down to Zero  ie a Glidepath.  It is a projection  for a chart.

81   -7

74 -13

61 etc