Forum Discussion
Getting Dates from Data into a Report Builder Report
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
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.
3 Replies
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:
- Verify that your dataset includes the required dates (6/30/2024, 9/30/2024, etc.) as fields or calculated columns.
Add a Header Row:
- Insert a row above the existing header row in the table if you don't already have a place for dates.
Use Expressions to Populate Dates:
- Click on the header cell where the date needs to appear (e.g., above the column "Actual Last Year").
- Right-click the cell and choose Expression.
Write the Expression for Each Column:
- Use conditional logic to fetch the correct date based on the column's context. For example:
- For "Actual Last Year":
=First(Fields!Date.Value, "YourDatasetName") ' Filter for 6/30/2024 data - For "Actual Current Year": =First(Fields!Date.Value, "YourDatasetName") ' Filter for 9/30/2024 data
- Repeat for other columns as needed.
- Replace "YourDatasetName" with the name of your dataset.
- For "Actual Last Year":
Exclude Variance Column:
- For the variance column ("Over/Under"), leave its header blank or set it to "Variance" manually.
Verify and Adjust:
- Preview the report to confirm the dates are correctly aligned.
- Ensure your dataset filters or calculations correctly match the fiscal year logic.
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.
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.
