Getting Dates from Data into a Report Builder Report

[callenbkd]

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

[callenbkd · Accepted 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.

[SamInogic]

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.

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!

[callenbkd]

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.