Calculating forecasting values.

[kalspiros]

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

[v-tangjie-msft]

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. 

[kalspiros]

Hi @v-tangjie-msft 

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

[kalspiros]

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

[Rupak_bi]

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.