Forum Discussion

vipinverma11's avatar
vipinverma11
Frequent Visitor
2 years ago

need help in dax

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
vipinverma11
Frequent Visitor
2 years ago

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