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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need some help to divide balance hours based on start date and due date

Hi All, 

 

Need some help to implement a report/dashboard that divides the total balance hours based on start date and end date . Currently I have no idea how to implement this on Power BI. 

 

Based on below sample data :

Hairul_Khumaini_0-1678346809199.png

 

What I want to achieve is : 

(1) I want to display a table similar to below but the all the hours are spread between the start date and end date .Currently below the hours are not spread based on start and end date

Hairul_Khumaini_1-1678346903620.png

 

I will convert this to chart later on but i would like first solve on how to implement this

 

Thanks and Regards,

Hairul

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 
Is this what you're looking for?

1.png

Distributed Hrs = 
SUMX ( 
    GENERATE ( 
        'Table',
        FILTER ( 
            VALUES ( 'Date'[Date] ), 
            'Date'[Date] >= 'Table'[Start Date]
                && 'Date'[Date] <= 'Table'[Due Date]
        )
    ),
    [Calculated_BalanceHrs] / 'Table'[No of Days]
)

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @tamerj1  For now i will just deactivate it . Thanks for the solution

 

Thanks and Regards,

Hairul

Anonymous
Not applicable

Hi @tamerj1 , in my current Power BI i have linked the relationship my Calendar table which is Calendar[Date] column with StartDate of table above . Is it possible I get the same result ? Once i linked these two columns it doesnt produce same result . I do this because my report will be filter by month and week later on .

 

 

This is my result currently :

 

Hairul_Khumaini_0-1678430392250.png

result =

SUMX (
GENERATE (
'PowerBI_MachinePlanning',
FILTER (
VALUES ( 'Calendar'[Date] ),
'Calendar'[Date] >= PowerBI_MachinePlanning[JobOper_StartDate]
&& 'Calendar'[Date] <= PowerBI_MachinePlanning[JobOper_DueDate]
)
),
(PowerBI_MachinePlanning[1 Calculated_BalanceHrs])/PowerBI_MachinePlanning[2. # Days]
)
 

Thanks and Regards,
Hairul

@Anonymous 

If you need this relationship for any reason, you may try

result =
SUMX (
GENERATE (
'PowerBI_MachinePlanning',
FILTER (
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
CROSSFILTER ( 'Calendar'[Date], PowerBI_MachinePlanning[JobOper_StartDate], NONE )
),
'Calendar'[Date] >= PowerBI_MachinePlanning[JobOper_StartDate]
&& 'Calendar'[Date] <= PowerBI_MachinePlanning[JobOper_DueDate]
)
),
( PowerBI_MachinePlanning[1 Calculated_BalanceHrs] ) / PowerBI_MachinePlanning[2. # Days]
)

otherwise just delete or deactivate it. 

Anonymous
Not applicable

Hi @tamerj1 , yes this is what i want . Let me try this solution first and will get back to you if its not working


Thanks and Regards,
Hairul

tamerj1
Super User
Super User

Hi @Anonymous 
Is this what you're looking for?

1.png

Distributed Hrs = 
SUMX ( 
    GENERATE ( 
        'Table',
        FILTER ( 
            VALUES ( 'Date'[Date] ), 
            'Date'[Date] >= 'Table'[Start Date]
                && 'Date'[Date] <= 'Table'[Due Date]
        )
    ),
    [Calculated_BalanceHrs] / 'Table'[No of Days]
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors