Forum Discussion
Complex conditions in a column
Hi!! I'm trying to add a column in a query, that either returns the value of beginning of the year (Opening Balance), year-to-date value (Changes) or beginning of the year value plus year-to-date...
- So like this?
Column (YTD) =
SWITCH([Code],
"AAA", VAR __Min = MINX(FILTER('Table',[Code]="AAA"),[Date])
RETURN LOOKUPVALUE('Table'[Value],'Table'[Code],"AAA",'Table'[Date],__Min),
"BBB", SUMX(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date]) && [Code]="BBB"),[Value]),
"CCC", 'Table'[Value],
BLANK()
)
Greg_Deckler
Community Champion
Community ChampionSo like this?
Column (YTD) =
SWITCH([Code],
"AAA", VAR __Min = MINX(FILTER('Table',[Code]="AAA"),[Date])
RETURN LOOKUPVALUE('Table'[Value],'Table'[Code],"AAA",'Table'[Date],__Min),
"BBB", SUMX(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date]) && [Code]="BBB"),[Value]),
"CCC", 'Table'[Value],
BLANK()
)
Column (YTD) =
SWITCH([Code],
"AAA", VAR __Min = MINX(FILTER('Table',[Code]="AAA"),[Date])
RETURN LOOKUPVALUE('Table'[Value],'Table'[Code],"AAA",'Table'[Date],__Min),
"BBB", SUMX(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date]) && [Code]="BBB"),[Value]),
"CCC", 'Table'[Value],
BLANK()
)
Thank you sooooo much! I've tried it and it was exactly what I've wanted.
However, what if we have several years in a query and want to get the beginning balance of the year or sum up only the changes during the year? Just like below?
| Code | Description | Date | Value | Value(YTD) |
| AAA | Opening balance | 01/01/2019 | 100 | 100 |
| AAA | Opening balance | 01/02/2019 | 110 | 100 |
| AAA | Opening balance | 01/03/2019 | 120 | 100 |
| BBB | Changes | 01/01/2019 | 10 | 10 |
| BBB | Changes | 01/02/2019 | 10 | 20 |
| BBB | Changes | 01/03/2019 | 10 | 30 |
| CCC | Ending Balance | 01/01/2019 | 110 | 110 |
| CCC | Ending Balance | 01/02/2019 | 120 | 120 |
| CCC | Ending Balance | 01/03/2019 | 130 | 130 |
| AAA | Opening balance | 01/01/2020 | 130 | 130 |
| AAA | Opening balance | 01/02/2020 | 150 | 130 |
| AAA | Opening balance | 01/03/2020 | 170 | 130 |
| BBB | Changes | 01/01/2020 | 20 | 20 |
| BBB | Changes | 01/02/2020 | 20 | 40 |
| BBB | Changes | 01/03/2020 | 20 | 60 |
| CCC | Ending Balance | 01/01/2020 | 150 | 150 |
| CCC | Ending Balance | 01/02/2020 | 170 | 170 |
| CCC | Ending Balance | 01/03/2020 | 190 | 190 |
- Quite forcefully, I managed to solve this way:Column (YTD) =SWITCH([Code];"AAA"; VAR _BB = STARTOFYEAR(Plan1[Date])RETURN LOOKUPVALUE('Table'[Value];'Table'[Code];"AAA";'Table'[Date];_BB);"BBB"; VAR _EB = Table[Date] VAR _BB = STARTOFYEAR(Plan1[Date])RETURN LOOKUPVALUE(Table[Value];Table[Code];"CCC";Table[Date]; _EB) - LOOKUPVALUE('Table'[Value];'Table'[Code];"AAA";'Table'[Date];_BB);"CCC"; 'Table'[Value];BLANK())