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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Yashraj
Frequent Visitor

Repeating Records of table

Hello All - I have a query which is a use case for me in Power BI. 

 

I have a table in excel that I update each month with Employee Name for the division. The list would be for 1300 unique people. Based on that table, we extrapolate the same as 1300 rows for the rest of the year  so that we can HC forecast  for full year. In next month, we would take the HC for the end of that month and then follow the same process. For Eg, if July HC is 1300, Full Year data for each month would be updated. Next month if the HC is 1200, then July would be 1300, August would be 1200 and then September to June would be 1200 for indicative budgets. To achieve that, I need to paste 1200 records for each month. Is there a way to just paste the data for that 1 month and then thru some calculations / DAX, forecast based on the previous month. 

1 ACCEPTED SOLUTION

To achieve this in Power BI, you can use DAX (Data Analysis Expressions) to create calculated columns and measures that will help you forecast the headcount (HC) for the rest of the year based on the most recent month's data.

Step 1: Load the Data

  1. Open Power BI Desktop.
  2. Click on "Get Data" and import your Excel file.
  3. Load the data into Power BI.

Step 2: Create Measures for Forecasting

You can create DAX measures to forecast the HC for future months based on the latest available data. Here's a step-by-step guide:

  1. Identify the Most Recent Month:
    1. Create a measure to get the most recent month with available data.
    2. dax formula =LatestMonth = MAX('EmployeeHC'[Month])
    3. Get the HC for the Most Recent Month:
      1. Create a measure to get the HC value for the most recent month.
      2. dax formula below
      3. HC_LatestMonth =
        CALCULATE(
        MAX('EmployeeHC'[HC]),
        FILTER(
        'EmployeeHC',
        'EmployeeHC'[Month] = [LatestMonth]
        )
        )
    4. Forecast HC for Future Months:
      • Create a measure to use the latest month’s HC value for future months.
      • dax expression below
      • ForecastedHC =
        VAR CurrentMonth = MAX('DateTable'[MonthYear])
        VAR LatestMonthWithData = MAX('EmployeeHC'[Month])
        RETURN
        IF(
        FORMAT(CurrentMonth, "YYYY-MM") <= FORMAT(LatestMonthWithData, "YYYY-MM"),
        CALCULATE(MAX('EmployeeHC'[HC]), 'EmployeeHC'[Month] = CurrentMonth),
        CALCULATE(MAX('EmployeeHC'[HC]), 'EmployeeHC'[Month] = LatestMonthWithData)
        )

Step 3: Create a Table for Forecasted HC

  1. Create a Date Table:
    • Go to Modeling > New Table and create a Date table if you don’t already have one.
    • dax expression below
    • DateTable =
      CALENDAR(
      DATE(YEAR(TODAY()), 1, 1),
      DATE(YEAR(TODAY()), 12, 31)
      )
  2. Create Relationships:

    • Create a relationship between your  own table and the DateTable based on the Month column.
  3. Visualize the Data:

    • Create a table or matrix visual and use the Month column from the DateTable and the ForecastedHC measure to display the forecasted HC values.

 

@Yashraj  just follow this steps and it will work.

 

Please accept this as a soultion once are done with it.

View solution in original post

6 REPLIES 6
Yashraj
Frequent Visitor

Here you... Left side is the data table in PBI, Right 3 columns are how the dat should look like. For the month when I update the data, it should consider same count for the rest of the financial year

 

Test_INfo.png

@Yashraj  

Jaytam_0-1717407975315.png

 

Above is the output I have done with sample data in power BI. is this way you are looking for?

IF so I can share the screenshot of it how I did it.

 

Yes @Jaytam .. This is what I am looking at

To achieve this in Power BI, you can use DAX (Data Analysis Expressions) to create calculated columns and measures that will help you forecast the headcount (HC) for the rest of the year based on the most recent month's data.

Step 1: Load the Data

  1. Open Power BI Desktop.
  2. Click on "Get Data" and import your Excel file.
  3. Load the data into Power BI.

Step 2: Create Measures for Forecasting

You can create DAX measures to forecast the HC for future months based on the latest available data. Here's a step-by-step guide:

  1. Identify the Most Recent Month:
    1. Create a measure to get the most recent month with available data.
    2. dax formula =LatestMonth = MAX('EmployeeHC'[Month])
    3. Get the HC for the Most Recent Month:
      1. Create a measure to get the HC value for the most recent month.
      2. dax formula below
      3. HC_LatestMonth =
        CALCULATE(
        MAX('EmployeeHC'[HC]),
        FILTER(
        'EmployeeHC',
        'EmployeeHC'[Month] = [LatestMonth]
        )
        )
    4. Forecast HC for Future Months:
      • Create a measure to use the latest month’s HC value for future months.
      • dax expression below
      • ForecastedHC =
        VAR CurrentMonth = MAX('DateTable'[MonthYear])
        VAR LatestMonthWithData = MAX('EmployeeHC'[Month])
        RETURN
        IF(
        FORMAT(CurrentMonth, "YYYY-MM") <= FORMAT(LatestMonthWithData, "YYYY-MM"),
        CALCULATE(MAX('EmployeeHC'[HC]), 'EmployeeHC'[Month] = CurrentMonth),
        CALCULATE(MAX('EmployeeHC'[HC]), 'EmployeeHC'[Month] = LatestMonthWithData)
        )

Step 3: Create a Table for Forecasted HC

  1. Create a Date Table:
    • Go to Modeling > New Table and create a Date table if you don’t already have one.
    • dax expression below
    • DateTable =
      CALENDAR(
      DATE(YEAR(TODAY()), 1, 1),
      DATE(YEAR(TODAY()), 12, 31)
      )
  2. Create Relationships:

    • Create a relationship between your  own table and the DateTable based on the Month column.
  3. Visualize the Data:

    • Create a table or matrix visual and use the Month column from the DateTable and the ForecastedHC measure to display the forecasted HC values.

 

@Yashraj  just follow this steps and it will work.

 

Please accept this as a soultion once are done with it.

Jaytam
Frequent Visitor

Hi Yashraj,

this you can do it by Dax query in power BI.

BeaBF
Super User
Super User

@Yashraj Hi! Can you paste sample data and the expected output?

 

BBF

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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