Forum Discussion

krrish116's avatar
krrish116
Icon for Resolver II rankResolver II
5 years ago
Solved

Spreading the Value from Start date to end date by Weekly

Hi amitchandak,

I have 2 tables

Allocation ; Requests

In Allocation

A-IDA-StartDateA-EndDateA-Hours
18/24/202012/31/202140
48/27/202012/31/20210.5
58/31/202012/31/202140
610/5/202012/31/202130
78/31/202012/31/20218
812/7/20203/19/202115
469/14/20201/29/20214
4412/7/202012/31/202130
4312/21/202012/31/20215
3912/7/202012/31/20215
3712/2/20201/31/20212
3611/25/202012/7/20208
3611/25/202012/7/20202
3511/11/202012/11/202010
3511/11/202012/11/202015

 

In Requests

TaskAssignedToHoursR-Start DateIDR-End Date
RepetitiveJohn408/24/2020112/31/2021
RepetitivePeter53/26/2021412/31/2021
Project 309/4/2020510/14/2020
RepetitiveZeus0.58/24/2020612/31/2021
RepetitiveJohn608/31/2020712/31/2021
RepetitiveBarang3010/5/2020812/31/2021
One-timeNarod110/13/2020461/31/2021
One-time  11/2/20204412/15/2020
One-timeZeus209/16/20204312/15/2020
One-time  8/10/20203911/18/2020
One-time  8/13/20203710/23/2020
ProjectBarang309/4/20203610/14/2020
ProjectNarod1611/23/2020363/26/2021
ProjectJohn57/6/20203511/10/2020
ProjectPeter611/6/2020321/31/2021

 

So, based on the above tables the Relationship is A-ID ->ID

 

Now i have to show a graph based on the above tables.

in that graph the dates are shown on weekly basis and week starts with Monday.

I have to calculate the sum of A-Hours for A-ID and TASK "Repetitive". From start to end date i have to show 

For Example :

A-ID 1 has 40 Hrs from  8/24/2020 to 12/31/2021

so for all these dates it has to show 40 in all bars like wise for

A-ID  4 has 0.5 Hrs from  8/27/2020 to 12/31/2021

so for all these dates it has to show 0.5 in all bars like wise for

So, i want to know how should i show and distribute the value to all dates in graph..

 

Thanks,

Krish.

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi krrish116 ,

     

    Based on your description, you can do some steps as follows.

    1. create a calendar table.

    Calendar =
    ADDCOLUMNS (
    CALENDAR (
    CALCULATE ( MIN ( 'Allocation'[A-StartDate] ), ALL () ),
    CALCULATE ( MAX ( 'Allocation'[A-EndDate] ), ALL () )
    ),
    "Yesr_week",
    YEAR ( [Date] ) & "-"
    & WEEKNUM ( [Date], 2 )
    )

    2. create two columns in "Calendar" table.

    AID_Repetitive_sum =
    VAR x1 =
    FILTER (
    ADDCOLUMNS (
    'Allocation',
    "task", LOOKUPVALUE ( Requests[Task], Requests[ID], [A-ID] )
    ),
    [task] = "Repetitive"
    )
    RETURN
    SUMX (
    FILTER ( x1, [Date] >= [A-StartDate] && [Date] <= [A-EndDate] ),
    [A-Hours]
    )
     X_axis_Rank =
    RANKX (
    ALL ( 'Calendar' ),
    VALUE ( LEFT ( [Yesr_week], 4 ) ) * 1000
    + WEEKNUM ( [Date], 2 ),
    ,
    ASC
    )

    3.Create a line chart.

    Result:

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi krrish116 ,

     

    Based on your description, you can do some steps as follows.

    1. create a calendar table.

    Calendar =
    ADDCOLUMNS (
    CALENDAR (
    CALCULATE ( MIN ( 'Allocation'[A-StartDate] ), ALL () ),
    CALCULATE ( MAX ( 'Allocation'[A-EndDate] ), ALL () )
    ),
    "Yesr_week",
    YEAR ( [Date] ) & "-"
    & WEEKNUM ( [Date], 2 )
    )

    2. create two columns in "Calendar" table.

    AID_Repetitive_sum =
    VAR x1 =
    FILTER (
    ADDCOLUMNS (
    'Allocation',
    "task", LOOKUPVALUE ( Requests[Task], Requests[ID], [A-ID] )
    ),
    [task] = "Repetitive"
    )
    RETURN
    SUMX (
    FILTER ( x1, [Date] >= [A-StartDate] && [Date] <= [A-EndDate] ),
    [A-Hours]
    )
     X_axis_Rank =
    RANKX (
    ALL ( 'Calendar' ),
    VALUE ( LEFT ( [Yesr_week], 4 ) ) * 1000
    + WEEKNUM ( [Date], 2 ),
    ,
    ASC
    )

    3.Create a line chart.

    Result:

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

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