Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Help with summing within a time period

Hi all.  

Seeking help with the following.

My data is a list of projects with a Start Date, End Date and Total Minutes.

 

My Matrix is a table with Week Commencing columns and rows as Impacted Teams.  

 

Where an initiative spans multiple weeks, the matrix needs to allocate the average of Total Minutes across the weeks.  For example, Initiative A, starting 08/07 and concluding 18/07 with a total of 180 minutes, should have 90 minutes each in Week Commencing 08/07 and 15/07. 

 

What is happening is just 90 minutes is being allocated to Week 15/07 (refer screenshot).

Any thoughts on why this is not working?

 

I am using the following Measure:

 

Distributed Minutes =

VAR CurrentWeek = SELECTEDVALUE('Date'[Start of Week (DD-MMM)])

VAR InitiativeStart = MIN(Query[Start Date])

VAR InitiativeEnd = MAX(Query[End Date])

VAR WeeksSpan = DATEDIFF(InitiativeStart, InitiativeEnd, WEEK)+1

VAR ProportionalMinutes = [Sum of Impact Size (Mins)] * DIVIDE(1, WeeksSpan, 0)

RETURN

    IF(CurrentWeek >= InitiativeStart && CurrentWeek <= InitiativeEnd, ProportionalMinutes, 0)

 

 

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Can you provide some sample data? We can better understand the problem and help you.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

    Best regards,
    Albert He

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