Calculating accros multiple columns and visualizing with a timeline

[PowerBI_User16]

Hi,
I have created a table with example data for YEAR (2022 - 2025), 10 different account numbers per year, company code, attributes (opening balance, january - december), value, debit (debit: positive), credit (credit: negative) The opening balance is the value from the previous year in december. The other values are the change in the month. I would like to calculate the following values in a new column. For example: January = 10500 February = 10300 March = 10600 April = 10500 May = 10350 etc.'
I have another column for Day_Month_Year, which has a relationship to a Mapping_date table. 

How can I calculate the correct value for each month and year and use a timeline to visualize the result?

YEAR ACCOUNT COMNPANY CODE ATTRIBUTE VALUE DEBIT/CREDIT

2022	10000	1000	Opening balance	10000.00	10000.00
2022	10000	1000	January	500.00	500.00
2022	10000	1000	February	-200.00	-200.00
2022	10000	1000	March	300.00	300.00
2022	10000	1000	April	100.00	100.00
2022	10000	1000	May	-150.00	-150.00
2022	10000	1000	June	200.00	200.00
2022	10000	1000	July	250.00	250.00
2022	10000	1000	August	-100.00	-100.00
2022	10000	1000	September	350.00	350.00
2022	10000	1000	October	-50.00	-50.00
2022	10000	1000	November	150.00	150.00
2022	10000	1000	December	400.00	400.00
2022	20000	1000	Opening balance	5000.00	5000.00
2022	20000	1000	Januar	-300.00	-300.00
...	...	...	...	...	...
2023	10000	1000	Opening balance	11750.00	11750.00
2023	10000	1000	January	600.00	600.00
...	...	...	...	...	...
2024	30000	1000	Anfangsbestand	8000.00	8000.00
2024	30000	1000	Januay	-200.00	-200.00
...	...	...	...	...	...
2025	40000	1000	Anfangsbestand	15000.00	15000.00
2025	40000	1000	January	800.00	800.00
...	...	...	...	...	...

I have introduced two calculated columns subcategories to categorize the data:

Subcategory =

SWITCH(

    TRUE,

    CALCULATE(COUNTROWS(FILTER('GLFUNCT', 'TABLE'[COMPANY CODE] IN {"1000"}))) > 0, "1. Productionmaterial",

    CALCULATE(COUNTROWS(FILTER('GLFUNCT', 'TABLE'[COMPANY CODE] IN {"2000", "3000"}))) > 0, "3. Consumables, supplies and goods",

     "2. UE_FE_Total"

)

Subcategory 1 =

SWITCH(

    TRUE,

    'TABLE'[COMPANY CODE] IN {"1000"}, "2.1 TEST",

    'TABLE'[COMPANY CODEr] IN {"2000"}, "2.2 TEST",

    'TABLE'[COMPANY CODE] IN {"3000", "4000"}, "2.3 TEST",

    BLANK()

)

[V-yubandi-msft]

Hi @PowerBI_User16 ,

We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.

Thank You.

[V-yubandi-msft]

Hi @PowerBI_User16 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

 

Thank you.

[V-yubandi-msft]

Hi @PowerBI_User16 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[V-yubandi-msft]

Hi @PowerBI_User16 ,

@bhanu_gautam logic is sound and provides a strong foundation. I have a couple of suggestions in line with Power BI best practices.

1. Instead of comparing month names (e.g., January <= February), it's more effective to create a separate Month Number column (with values 1 to 12). This approach ensures your calculations are accurate and your visuals are sorted correctly.
2. For improved performance and flexibility, consider using a DAX measure rather than a calculated column, especially when your calculation must adjust based on context (for instance, filtering by year, account, etc.). Measures are evaluated at query time, offering more dynamic behaviour for your visuals.

Helpful Reference: Learn DAX basics in Power BI Desktop - Power BI | Microsoft Learn

Thnaky you for your inputs @bhanu_gautam .

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[bhanu_gautam]

@PowerBI_User16 First, create a calculated column to compute the running total for each month. This column will add the monthly changes to the opening balance.

RunningTotal =
VAR CurrentYear = 'TABLE'[YEAR]
VAR CurrentAccount = 'TABLE'[ACCOUNT]
VAR CurrentMonth = 'TABLE'[ATTRIBUTE]
VAR OpeningBalance = CALCULATE(
MAX('TABLE'[VALUE]),
'TABLE'[ATTRIBUTE] = "Opening balance",
'TABLE'[YEAR] = CurrentYear,
'TABLE'[ACCOUNT] = CurrentAccount
)
VAR MonthlyChanges =
CALCULATE(
SUM('TABLE'[VALUE]),
'TABLE'[YEAR] = CurrentYear,
'TABLE'[ACCOUNT] = CurrentAccount,
'TABLE'[ATTRIBUTE] <= CurrentMonth,
'TABLE'[ATTRIBUTE] <> "Opening balance"
)
RETURN
OpeningBalance + MonthlyChanges

Ensure you have a date table to map the Day_Month_Year column. This table should include all dates within the range of your data.

DAX
DateTable =
CALENDAR(
MIN('TABLE'[Day_Month_Year]),
MAX('TABLE'[Day_Month_Year])
)

Create a relationship between the Day_Month_Year column in your main table and the date column in the DateTable.

Use a line chart to visualize the running total over time. Set the Day_Month_Year from the DateTable as the axis and the RunningTotal as the values.

You already have the subcategories calculated. Ensure these are used as needed in your visualizations to filter or categorize the data.