Hour Distribution from Current Date to Max Plan Date

[AllanBerces]

Hi Good day,

Can anyone pls need help and assistance on my data. I want to add the increase of hour on my plan from current date to max date of my plan, the tricky part its keep on changing almost everyday.

So let say base the table below my base hrs is 565hrs and increase to 856hrs(become new base) the difference to be equally distributed each date to max date. 

If on the following day increase let say 900hrs - 856 = 44 to be distribute. 

The main goal is if there an increase of hours should equal to my total(new base).

my measure

Thank you in advance

[v-jingzhan-msft · Accepted solution]

Hi @AllanBerces 

You can try the following column. Is this what you want?

Column = 
VAR newHours = 856
VAR BaseHours = 565
VAR maxDate = CALCULATE(MAX('Table'[Date]),ALL('Table'))
VAR days = DATEDIFF(TODAY(),maxDate,DAY) + 1
RETURN
IF('Table'[Date]>=TODAY(),
    DIVIDE(newHours-BaseHours,days))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Greg_Deckler]

@AllanBerces Maybe:

Measure = 
  VAR __New = [New Hrs]
  VAR __Base = [Base Hrs]
  VAR __Diff = __New - __Base
  VAR __Today = TODAY()
  VAR __MaxDate = MAX('Table'[Date])
  VAR __Table = FILTER(ALL('Table'), [Date] >= __Today && [Date] <= __MaxDate)
  VAR __Count = COUNTROWS(__Table)
  VAR __Result = DIVIDE( __Diff, __Count, 0 )
RETURN
  __Result

[AllanBerces]

here the ouput.

[AllanBerces]

Hi Greg_Deckler,

Thank you for your respond, but the result are cumulative and reverse order, different from what i required to distribute the hour equally. from today to max date.

Pls need some help.

Thank you in advance.