Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
callenbkd
Regular Visitor

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: 

 

DateScenarioTypeType Sort Amount 
6/30/2024ActualRevenue1     50,000.00
6/30/2024ActualExpenses2     30,000.00
6/30/2024ActualOther Income/Expense3        5,000.00
9/30/2024ActualRevenue1     60,000.00
9/30/2024ActualExpenses2     45,000.00
9/30/2024ActualOther Income/Expense3        9,000.00
6/30/2025BudgetRevenue1     75,000.00
6/30/2025BudgetExpenses2     50,000.00
6/30/2025BudgetOther Income/Expense3        6,000.00
6/30/2025ForecastRevenue1     80,000.00
6/30/2025ForecastExpenses2     60,000.00
6/30/2025ForecastOther Income/Expense3        7,000.00
6/30/2026ForecastRevenue1     90,000.00
6/30/2026ForecastExpenses2     75,000.00
6/30/2026ForecastOther Income/Expense3        8,000.00
6/30/2027ForecastRevenue1  100,000.00
6/30/2027ForecastExpenses2     80,000.00
6/30/2027ForecastOther Income/Expense3     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 YearCurrent YearCurrent YearCurrent Year   
 ActualActualBudgetForecastVarianceForecastForecast
Type6/30/20249/30/20246/30/20256/30/2025 6/30/20266/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. 

 

report example.png

 

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

1 ACCEPTED SOLUTION
callenbkd
Regular Visitor

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.

View solution in original post

3 REPLIES 3
callenbkd
Regular Visitor

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
Super User
Super User

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!

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.