Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
17 | |
16 |
User | Count |
---|---|
28 | |
27 | |
18 | |
14 | |
14 |