Showing monthly actual data and forecast data

[metcala]

Hi everyone

I have a problem that I am struggling with and can see similar topics that have been resolved but not exactly the same.

I was wondering if anyone can point me in the right direction.

Issue

Displaying current financial year month actuals up until current month and then forecast for future months

Data structure

• Date table
  Date
  Calendar Year
  Month Name
  Month Number
  
  
• Employees
  Employee ID
  Title
  Name
  Start Date
  End Date
  ...
  EmployeeCosts (Measure)
  
  

EmployeeCosts =
CALCULATE( SUM( 'Employees'[Monthly Costs]),
   FILTER( 'Employees', 'Employees'[Start Date] <= MAX( 'Date'[Date]) && ( ISBLANK( 'Employees'[End Date]) || 'Employees'[End Date] > MAX ('Date'[Date]))), ('Employees'[Title)))​

• Payroll Actuals
  ...
  Payroll End Date
  Employee ID
  Monthly Cost
  PayrollCosts (Measure)
  
  

PayrollCosts =
CALCULATE( SUM( 'Payroll'[Actual],
   FILTER( 'Employees', 'Employees'[Start Date] <= MAX( 'Date'[Date]) && (ISBLANK('Employees'[End Date]) || 'Employees'[End Date] > MAX( 'Date'[Date]))), ('Employees'[Title]),
USERELATIONSHIP( 'Date'[Date], Payroll[Payroll End Date]))​

Current attempt

So I have tried resolve this with a measure on Payroll as follows but it only is showing the actuals

ActualForecasts =
IF( SELECTEDVALUE( 'Payroll'[Payroll End Date] <= MONTH( TODAY()),
'Payroll'[PayrollCosts],'Employees'[EmployeeCosts])

Any help/guidance would be much appreciated!

[v-zhangti]

Hi, @metcala 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[metcala]

@v-zhangti 

Hi there thanks for getting back, hopefully this makes a little bit more sense....

Employee table

Employees

Payroll table

Payroll actual

Expected result

Expected Result

I have tested the logic for the measures in my original message which appears to be correct. I just want to check if there is actual payroll data and if so use that otherwise revert to the total in the employee table for the expected monthly cost.

Sorry for not being clear in my initial message.

Thanks in advance!

[v-zhangti · Accepted solution]

Hi, @metcala 

You can try the following methods.

Add a new month-end date table to make the column of the matrix.

Measure =
VAR _actual =
    CALCULATE (
        SUM ( Payroll[Monthly Actual Cost] ),
        FILTER (
            ALL ( Payroll ),
            [Employee ID] = SELECTEDVALUE ( Payroll[Employee ID] )
                && [Payroll End Date] = SELECTEDVALUE ( 'Date'[Month end] )
        )
    )
VAR _forecast =
    CALCULATE (
        SUM ( Employee[Expected Monthly Salary] ),
        FILTER (
            ALL ( Employee ),
            [Employee ID] = SELECTEDVALUE ( Payroll[Employee ID] )
        )
    )
RETURN
    IF ( _actual = BLANK (), _forecast, _actual )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[metcala]

Thank you very much, that has worked perfectly!!