need help in dax

[vipinverma11]

Hi,

I am looking to create a measure for the below table, I have date column with respect to workcenter number, total labour hour will tell you the booked hours on that date & WC_ hours is hours of working. 

I want a solution if total labour hour cross the WC_hours i.e 35, then the extra hours will add into the next date labour hours then next date will compare from 35 and whatever hours is greater than 35 hours then add to next date, otherwise 0 and so on.

For example: 8-feb data, the spill over of 8-feb will add into 9 feb hours like 9th feb hours should be 34.5+ 8.40(previous day spill over) so that become 42.5 then this date will compare from 35 then extra hours will add into next day labour hours & this process will continue. 

How can i acheive this?

[Anonymous]

Hi, @vipinverma11 

Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

spill_over = 'Table'[total labour hours] - 'Table'[WC_hours]



new spill_over =

VAR _laterSecondCell =

    CALCULATE (

        MAX ( 'Table'[spill_over] ),

        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )

    )

RETURN

    _laterSecondCell



New total labour hours =

CALCULATE (

    SUM ( 'Table'[total labour hours] ) + SUM ( 'Table'[new spill_over] )

)

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[vipinverma11]

Hi Can you help me on this, how can i achevie this either in power query, measure or calculated column. this solution can be acheive if power can store the total labour hour + shifted spill over = new labour hour against that new spill over will come and then it will add into new labour hour and this process will go on if power bi can able to store the value in virtual table

[vipinverma11]

Thanks for your help, i have just tweak the formula and added one more filter condition that opworkcentre=earlier[opworkcentre] then i have achieved this

But in this i want to acehieve like if you see the data of 19 jan & 22 jan. So 19th jan spill over that 4.95 it's added in 22 jan labour hours that is 37.97+ 4.95 = 42.92 which is correct which i am getting in the last column.

So after that next step would in next row it will compare new labour hour 42.92 is greater than 35 then 42.92-35 = 7.92 should add in to the 23 jan labour hour 24.4+ 7.92 = 32.32 so in this no spill over so i should get 0,  and so on .This process will carry forward. 

I am confused is we have to create a calculated column or measure becuase in calculated column the values will be static

[vipinverma11]

Hi, 

Thanks for your efforts, i am not able to achevive the result, for spill over i have used the formula "

"

Spill_over = IF('Current& Future Steps SQL (2)'[total labour hours]>'Current& Future Steps SQL (2)'[WC_hours],'Current& Future Steps SQL (2)'[total labour hours]-'Current& Future Steps SQL (2)'[WC_hours],0)

"

I have exported the Data for only one workcentre which will show 200 right now. same we have other workcentre as well and i have used index formula 

Index = RANKX(FILTER(ALL('Current& Future Steps SQL (2)'), 'Current& Future Steps SQL (2)'[OP Workcenter] = EARLIER('Current& Future Steps SQL (2)'[OP Workcenter])), 'Current& Future Steps SQL (2)'[OP Start Date], , ASC) to start indexing from 1 to 365 for each work centre.

I am attaching the dataset in reply post

[vipinverma11]

Sample Data  I have uploaded in the drive

[Anonymous]

Do you know how to use the IF function in DAX?

[vipinverma11]

same formula you can use if the condition to use if in measure there should be any  calculation, like sum , max etc. if you want to pass the column then you have to use selected value to pass any column refrence. 
But in calculated column you can pass the column