Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |