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 value (Ending Balance).
Present query looks like this:
| Code | Description | Date | Value |
| AAA | Opening balance | 01/01/2019 | 100 |
| AAA | Opening balance | 01/02/2019 | 110 |
| AAA | Opening balance | 01/03/2019 | 120 |
| BBB | Changes | 01/01/2019 | 10 |
| BBB | Changes | 01/02/2019 | 10 |
| BBB | Changes | 01/03/2019 | 10 |
| CCC | Ending Balance | 01/01/2019 | 110 |
| CCC | Ending Balance | 01/02/2019 | 120 |
| CCC | Ending Balance | 01/03/2019 | 130 |
The column I would like to add looks like this:
| Code | Description | Date | Value | Column(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 |
I tried to insert a DAX like this but didn't work:
IF ( Code = "AAA" , CALCULATE ( CALCULATE ( Value , ALL (DATE) ) , DATE = "01/01/2019" ) ,
IF ( Code = "BBB" , CALCULATE ( TOTALYTD ( Value , Date ) , OR ( CODE <> "AAA" , CODE <> "CCC" ) ) ,
CALCULATE ( CALCULATE ( Value , ALL (DATE) ) , DATE = "01/01/2019" + CALCULATE ( TOTALYTD ( Value , Date ) , OR ( CODE <> "AAA" , CODE <> "CCC" ) ) )
I'd appreciate if anyone could save me...! Thank you.
Yasuchiki
- 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()
)
3 Replies
- Greg_Deckler
Community Champion
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()
)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())