Calculate the amount of hours

[RonaldvdH]

I have a table with the weeks in it and the forecasted amount of hours as wel as the actual amount of hours spend on a project.

However now I want to calculate the Prognoses End Work, meaning the total amount of hours at the End.

Now if Today = 2023-W16 then I need a formula that calculates the amount of hours <= 2023-W16 and adds the amount of Hours > 2023-W17

So if the amount of hours <=2023-W16 is 100 hours and >2023-W17 is 300 the measure should return 400 hours

I want that formula as a measure and not a calculated column

[v-yueyunzh-msft]

Hi , @RonaldvdH 

According to your description, you want to calcualte the " if Today = 2023-W16 then I need a formula that calculates the amount of hours <= 2023-W16 and adds the amount of Hours > 2023-W17".

For your needs, I don't quite understand your business needs, you want to calculate the sum of <=2023-W16 and >2023-W17, this judgment logic is completely covered for your table, which makes no difference in summing the entire table using the SUM() function.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[RonaldvdH]

@v-yueyunzh-msft the catch is that I have a column in which I have the estimated spend hours and another column with the actual spend hours in it. I hope this table clarifies the problem.

My PEW hours would be:

Fore example today is 2023-W15 so....

SUM(Spend hours in the weeks < NOW)+SUM(Estimated hours in the weeks > NOW)

Spend hours < NOW =

2023-W11 t/m 2023-W15 = 32,5 Hours

Estimated hours > NOW=

2023-W16 t/m 2023-W18 = 30 hours.

PEW hours would be 32,5 +30 = 62,5 hours

	Estimated hours	Spend hours
2023-W11	10	7,5
2023-W12	10
2023-W13	10	4
2023-W14	10	15
2023-W15	10	6
2023-W16	10
2023-W17	10
2023-W18	10

[v-yueyunzh-msft]

Hi, @RonaldvdH 

Thanks for your quick response and your sample data!

Here are the steps you can refer to :
(1)Today is the 2023-Week16, So we can create a measure like this:

Measure = var _current_week = WEEKNUM(TODAY())
var _week  = "2023-W"&_current_week
var _before =  CALCULATE( SUM('Table'[Spend hours]) , 'Table'[Week]<= _week)
var _after =  CALCULATE( SUM('Table'[Estimated hours]) , 'Table'[Week]> _week)
return
_before+_after

(2)Then we can put this measure on the card visual and we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

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

[RonaldvdH]

@v-yueyunzh-msft That almost did the trick, wel actually it worked like a charm but ....

The variable _week is limited to weeknumbers in 2023 and I have also data from 2021 and 2022.

And the formula should be more dynamic that it takes in account the current Data and then Formats it to YYYY-"W"WW (1 should be formated as 01, so 2023-W01)

Is that possible ? because then i'll have a measure that is dynamic

Unfortunately I can't build a relationship between this Table and my Date table because the only reference between both tables is the Week but they are not unique in either table (relationship many to many).

[v-yueyunzh-msft · Accepted solution]

Hi ,  @RonaldvdH 

Thanks for your quick response! According to your description, you want to "the formula should be more dynamic that it takes in account the current Data and then Formats it to YYYY-"W"WW (1 should be formated as 01, so 2023-W01)".
If this , you can try to use this dax :

Measure = var _current_week = WEEKNUM(TODAY())
var _week  =YEAR(TODAY())& "-W"&FORMAT( _current_week, "00")
var _before =  CALCULATE( SUM('Table'[Spend hours]) , 'Table'[Week]<= _week , VALUE(LEFT('Table'[Week],4))=YEAR(TODAY()))
var _after =  CALCULATE( SUM('Table'[Estimated hours]) , 'Table'[Week]> _week, VALUE(LEFT('Table'[Week],4))=YEAR(TODAY()))
return
_before+_after

In this dax code , we can make the week1 in 2023 to 2023-W01 and we can make the YYYY-W00 dynamic.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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