Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Greetings.
I'm building a report in Report Builder. The source data is formatted like this:
Date | Scenario | Type | Type Sort | Amount |
6/30/2024 | Actual | Revenue | 1 | 50,000.00 |
6/30/2024 | Actual | Expenses | 2 | 30,000.00 |
6/30/2024 | Actual | Other Income/Expense | 3 | 5,000.00 |
9/30/2024 | Actual | Revenue | 1 | 60,000.00 |
9/30/2024 | Actual | Expenses | 2 | 45,000.00 |
9/30/2024 | Actual | Other Income/Expense | 3 | 9,000.00 |
6/30/2025 | Budget | Revenue | 1 | 75,000.00 |
6/30/2025 | Budget | Expenses | 2 | 50,000.00 |
6/30/2025 | Budget | Other Income/Expense | 3 | 6,000.00 |
6/30/2025 | Forecast | Revenue | 1 | 80,000.00 |
6/30/2025 | Forecast | Expenses | 2 | 60,000.00 |
6/30/2025 | Forecast | Other Income/Expense | 3 | 7,000.00 |
6/30/2026 | Forecast | Revenue | 1 | 90,000.00 |
6/30/2026 | Forecast | Expenses | 2 | 75,000.00 |
6/30/2026 | Forecast | Other Income/Expense | 3 | 8,000.00 |
6/30/2027 | Forecast | Revenue | 1 | 100,000.00 |
6/30/2027 | Forecast | Expenses | 2 | 80,000.00 |
6/30/2027 | Forecast | Other Income/Expense | 3 | 13,500.00 |
The end result in Report Builder will have a column for a variance using the current year forecast less the current year budget. The fiscal year starts 7/1 so 6/30/24, in this case, is the prior year and 9/30/24 is the current year.
Here's how the data should look:
Prior Year | Current Year | Current Year | Current Year | ||||
Actual | Actual | Budget | Forecast | Variance | Forecast | Forecast | |
Type | 6/30/2024 | 9/30/2024 | 6/30/2025 | 6/30/2025 | 6/30/2026 | 6/30/2027 | |
Revenue | 50,000.00 | 60,000.00 | 75,000.00 | 80,000.00 | 5,000.00 | 90,000.00 | 100,000.00 |
Expenses | 30,000.00 | 45,000.00 | 50,000.00 | 60,000.00 | 10,000.00 | 75,000.00 | 80,000.00 |
Other Income/Expenses | 5,000.00 | 9,000.00 | 6,000.00 | 7,000.00 | 1,000.00 | 8,000.00 | 13,500.00 |
Net Income | 15,000.00 | 6,000.00 | 19,000.00 | 13,000.00 | (6,000.00) | 7,000.00 | 6,500.00 |
We've created measures to set the amounts.
We're trying to add the dates from column 1 of the table to the report. I cannot figure out how to add the date to the report.
If we were using a matrix, it's straightforward. But, unless there's a way to insert the variance between columns in a matrix, we're using a table.
If somebody can tell me how to add the date into the header for each column, excluding the Over/Under column, I would be grateful.
Thank you
Solved! Go to Solution.
I think I have figured out a solution.
For the Actual Last Year, I used MIN(Date), which, right now, is 6/30/24. For the three This Year columns, I'm using First(Fields!Date.Value, "DataSet1"). For future years, I'm taking the MIN(Date) and adding 2, 3, 4, etc. then adding the 6/30 portion.
I think I have figured out a solution.
For the Actual Last Year, I used MIN(Date), which, right now, is 6/30/24. For the three This Year columns, I'm using First(Fields!Date.Value, "DataSet1"). For future years, I'm taking the MIN(Date) and adding 2, 3, 4, etc. then adding the 6/30 portion.
Hi,
As per our understanding, to include the dates in the header for each column in a Report Builder table, while excluding the "Over/Under" (variance) column, you can achieve this by using expressions in the header row of the table.
Here’s how to add dynamic dates to the table header:
Steps to Add Dates Dynamically to Table Headers
Ensure Dates are Accessible:
Add a Header Row:
Use Expressions to Populate Dates:
Write the Expression for Each Column:
Exclude Variance Column:
Verify and Adjust:
Thanks!
Thank you for the quick message.
While adding the expression isn't a problem, I think the answer is based on the date remaining as is. Is that right? I should have added that the data will be replaced each month. Until this fiscal year ends on 6/30/25, the Actual Current Year column date value will change from 9/30/24 to 10/31/24, etc. The dates for the other columns will remain the same until July 2025.
It seems like I could create a measure in Power BI to get the dates but I don't know how to write the expression. However, if it could be done using a calculated field or expression in Report Builder, I can use it.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
141 | |
115 | |
111 | |
59 | |
59 |