topic Re: Calculating forecasting values. in DAX Commands and Tips

Calculating forecasting values.

kalspiros — Mon, 21 Oct 2024 09:42:18 GMT

Hello all,

I have a measure that works all great.

The measure takes the deadline of projects and based on this, it calculates the number of working days and multiplying with the hourly rate of the employee and the expected working hours per week.

Here it is:

=-- Today, brought forward from Power Query VAR filteredMin = MIN('Calendar'[Date]) -- Set by the client, forecasting period VAR filteredMax = MAX('Calendar'[Date]) -- Deadline of Project VAR deadlineProject = MAX(Quote_Items[Deadline]) -- Repeating deadline of Project in all months. VAR deadlineWhole = CALCULATE( MAX(Quote_Items[Deadline]), ALL('Calendar')) -- Calculation of accurate date to be used to calculate working days. VAR deadlineMonth = IF( ISBLANK(deadlineProject),IF( deadlineWhole < filteredMax,BLANK(),filteredMax), deadlineProject) -- Filter the 'Calendar' table to include only working days between filteredMin and deadlineMonth VAR workingDaysCount = COUNTROWS( FILTER( 'Calendar', 'Calendar'[Date] >= filteredMin && 'Calendar'[Date] <= deadlineMonth && WEEKDAY('Calendar'[Date], 2) <= 5 -- Weekday function, with 2 means Monday = 1, Sunday = 7. Excludes weekends ) ) -- Returns the hours worked per week on that project VAR maxHours = CALCULATE(MAX(Quote_Items[Hours per week]),ALL('Calendar')) -- Returns the rate of the employee for this project VAR rateOfEmployee = CALCULATE(MAX(Quote_Items[Rate]),ALL('Calendar')) -- Final result, multiplying by the working days. VAR result = workingDaysCount * maxHours * rateOfEmployee RETURN result

The problem I'm facing is that it doesn't calculate Totals correctly (which makes sense, I think i should SUMX each of the values).

Any ideas on how I can change this measure to return the correct SUM?

Many thanks

Re: Calculating forecasting values.

Rupak_bi — Mon, 21 Oct 2024 11:18:26 GMT

Hi kalspiros ,

To get correct sum, you need to replace the "max" functions to "sum" function whenever applicable based on the operations you are performing in each variables. review all the calculate expressions and modify to ge the righr total.
Else share sample data.

Re: Calculating forecasting values.

kalspiros — Mon, 21 Oct 2024 13:10:59 GMT

Hi Rupak_bi

Thank you for your message. Actually 'sum' won't do the cut I'm afraid.

workingDaysCount is one of the culprits as it'll return an ubnormal count of days in the Grand Total.

Additionally, a sum of rateOfEmployees won't return the accurate number as it depends on how many hours each of the employee will work on each project.

Please find attached the file with some sample data and the measures (mainly interested on "Price")

Many thanks

PS: not sure how i can share sample file and couldn't find anything relevant here: https://community.fabric.microsoft.com/t5/Using-the-Community/tkb-p/fbc_using_the_community

Re: Calculating forecasting values.

Anonymous — Tue, 22 Oct 2024 02:12:35 GMT

Hi kalspiros ,

First of all thanks to Rupak_bi for the quick reply. I have some other thoughts to add:

You can create a new measure to be placed on the visual object instead of the original measure, totaling the correct display.

New Measure = SUMX(VALUES('Table'[Deadline]),[Measure])

If the above one can't help you get the desired result, please provide some sample data. It is better if you can share a simplified pbix file.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

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

Re: Calculating forecasting values.

kalspiros — Tue, 22 Oct 2024 12:34:30 GMT

Hi Anonymous

Thanks for your reply and providing these helpful pages.

In all honesty, I'm working on Excel.

I have just recreated the model in PBI but my original measure does not work as expected.

Therefore, I'm providing link to both files and I'd be probably more interested on exploring the .xlsx instead.

Your measure didn't quite work as expected I'm afraid.

Please feel free having a look and let me know if there's anything more I can do to assist.

forecasting.pbix

Forecasting_share.xlsx

Many thanks

Kind Regards