Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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....
Need Help
  • Jaytam's avatar
    Jaytam
    2 years ago

    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.

     

    Anonymous  just follow this steps and it will work.

     

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

 

Jaytam's avatar
Jaytam
Frequent Visitor
2 years ago

Anonymous  

 

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Yes Jaytam .. This is what I am looking at

    • Jaytam's avatar
      Jaytam
      Frequent Visitor
      2 years ago

      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.

       

      Anonymous  just follow this steps and it will work.

       

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