Complex conditions in a column

[Anonymous]

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

[Greg_Deckler · Accepted solution]

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()
)

[Anonymous]

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

[Anonymous]

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()

)