topic Re: Showing monthly actual data and forecast data in DAX Commands and Tips

Showing monthly actual data and forecast data

metcala — Mon, 14 Nov 2022 09:35:23 GMT

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!

Re: Showing monthly actual data and forecast data

v-zhangti — Tue, 15 Nov 2022 01:42:33 GMT

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.

Re: Showing monthly actual data and forecast data

metcala — Tue, 15 Nov 2022 12:37:53 GMT

v-zhangti

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

Employee table

Payroll table

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!

Re: Showing monthly actual data and forecast data

v-zhangti — Fri, 18 Nov 2022 09:22:38 GMT

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.

Re: Showing monthly actual data and forecast data

metcala — Fri, 18 Nov 2022 10:08:46 GMT

Thank you very much, that has worked perfectly!!