Forum Discussion
Calculating accros multiple columns and visualizing with a timeline
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()
)
5 Replies
- bhanu_gautam
Super User
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 + MonthlyChangesEnsure 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.
V-yubandi-msftCommunity Support
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.
- 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.
- 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.
- 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 ,
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 ,
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.
