Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
kalspiros
Helper I
Helper I

Calculating forecasting values.

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).

kalspiros_0-1729503603918.png

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

 

Many thanks

4 REPLIES 4
v-tangjie-msft
Community Support
Community Support

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])

 

vtangjiemsft_0-1729562925711.png

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. 

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
Helper I
Helper I

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
Post Prodigy
Post Prodigy

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.



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.