I want to get the value of “Beg of year” column for 2015 from the column “Open at the end” and Year 2014, same as shown in below snapshot
| Year | Beg of year | Report in year | Close | Total Inven | Open at the end |
|---|---|---|---|---|---|
| 2014 | 0 | 94 | 0 | 94 | 94 |
| 2015 | 94 | 102 | 12 | 196 | 184 |
| 2016 | 184 | 49 | 42 | 233 | 191 |
| 2017 | 191 | 49 | 51 | 240 | 189 |
| 2018 | 189 | 62 | 25 | 251 | 226 |
| 2019 | 226 | 51 | 27 | 277 | 250 |
| 2020 | 250 | 29 | 34 | 279 | 245 |
| 2021 | 245 | 16 | 21 | 261 | 240 |
| 2022 | 240 | 21 | 16 | 261 | 240 |
| 2023 | 245 | 8 | 25 | 253 | 228 |
| 2024 | 228 | 481 | 2 | 709 | 707 |
Note: Column Total Inven = Beg of year + report in year
Column open at the end = Total inven - Close
Open at the begining of Cal year = MAXX(
FILTER(
ALLSELECTED(‘Claims Data (2)’),’Claims Data (2)'[Year]=’Claims Data (2)'[Calender year]),[Open at the end])
This give the following error because column “Open at the end” is calculated from :
Total inven = Beg of year + report in year
A circular dependency was detected: Measure: ‘Claims Data (2)'[Total
Inventory], Measure: ‘Claims Data (2)'[Open at the beg], Measure:
‘Claims Data (2)'[Open at the end], Measure: ‘Claims Data (2)'[Total
Inven]
2
you can try this
Column =
SUMX (
FILTER ( 'Table', 'Table'[Year] <= EARLIER ( 'Table'[Year] ) ),
'Table'[Report in year] - 'Table'[Close]
)
beg of year2 = maxx(FILTER('Table','Table'[Year]=EARLIER('Table'[Year])-1),'Table'[Column])+0
3