Forum Discussion

rylach's avatar
rylach
Icon for Helper II rankHelper II
6 years ago
Solved

Calculating amount given for period - from one row

Hi, I have the following table, containing an amount per day in the following format: item (amount_per_day regards particular item) start_date end_date include_holidays (true/false), calenda...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi rylach ,

     

    We can use the following steps to meet your requirement.

     

    1. Create a date table first.

     

    Date = CALENDARAUTO()

     

     

    2. Then create four columns in Table to calculate the start date and end date.

     

    Next_start_week = [start_date]+7
    Next_end_week = [end_date]+7
    R_start_week = [Next_start_week]+7
    R_end_week = [Next_end_week]+7

     

     

    3. At last we can create a calculate table and create a table visual to get the result,

     

    Table 2 = 
VAR x =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[start_date] <= 'Date'[Date]
                && 'Table'[end_date] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR y =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[Next_start_week] <= 'Date'[Date]
                && 'Table'[Next_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR z =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[R_start_week] <= 'Date'[Date]
                && 'Table'[R_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
RETURN
ADDCOLUMNS ( UNION ( x, y, z ), "month", FORMAT ( [Date], "mmmm" ) )

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

v-zhenbw-msft's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi rylach ,

 

We can use the following steps to meet your requirement.

 

1. Create a date table first.

 

Date = CALENDARAUTO()

 

 

2. Then create four columns in Table to calculate the start date and end date.

 

Next_start_week = [start_date]+7
Next_end_week = [end_date]+7
R_start_week = [Next_start_week]+7
R_end_week = [Next_end_week]+7

 

 

3. At last we can create a calculate table and create a table visual to get the result,

 

Table 2 = 
VAR x =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[start_date] <= 'Date'[Date]
                && 'Table'[end_date] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR y =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[Next_start_week] <= 'Date'[Date]
                && 'Table'[Next_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR z =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[R_start_week] <= 'Date'[Date]
                && 'Table'[R_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
RETURN
ADDCOLUMNS ( UNION ( x, y, z ), "month", FORMAT ( [Date], "mmmm" ) )

 

 

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

rylach's avatar
rylach
Icon for Helper II rankHelper II
6 years ago

And the final code, with three kinds of periods, recurring up to recurring_end_date):

Table 3 = 

VAR crossjoined =

 SUMMARIZE (
   FILTER (
     CROSSJOIN ( 'Date'; 'Table' );
     'Date'[Date] >= 'Table'[start_date] 
     && 'Date'[Date] <= 'Table'[recurring_end_date] + DATEDIFF('Table'[start_date]; 'Table'[end_date];DAY)
 );

 'Table'[item];
 'Table'[RULE];
 [Date];
 'Table'[start_date];
 'Table'[end_date];
 'Table'[recurring_end_date];
 'Table'[amount per day]
 )
// RULE contains strings 'NEVER','WEEKLY','BIWEEKLY','MONTHLY'
VAR never = FILTER(crossjoined; 'Table'[RULE] = "NEVER" &&
 'Date'[Date] >= 'Table'[start_date] && 'Date'[Date] <= 'Table'[end_date]
)

VAR weekly = FILTER(crossjoined; 'Table'[RULE] = "WEEKLY"
 && 'Date'[Date] >= 'Table'[start_date] 
                 + DATEDIFF('Table'[start_date];'Date'[Date];WEEK) * 7 
 &&
 'Date'[Date] <= 'Table'[start_date]
                 + DATEDIFF('Table'[start_date];'Date'[Date];WEEK) * 7
                 + DATEDIFF('Table'[start_date];'Table'[end_date];DAY)
)

VAR biweekly = FILTER(crossjoined; 'Table'[RULE] = "BIWEEKLY" &&
 'Date'[Date] >= 'Table'[start_date] 
                 + INT(DIVIDE(DATEDIFF('Table'[start_date];'Date'[Date];DAY);14)) * 14
 &&
 'Date'[Date] <= 'Table'[start_date]
                 + INT(DIVIDE(DATEDIFF('Table'[start_date];'Date'[Date];DAY);14)) * 14
                 + DATEDIFF('Table'[start_date];'Table'[end_date];DAY)
)

VAR monthly = FILTER(crossjoined; 'Table'[RULE] = "MONTHLY" &&
 'Date'[Date] >= DATE( YEAR('Table'[start_date]);
                       MONTH('Table'[start_date]) + DATEDIFF('Table'[start_date];'Date'[Date];MONTH);
                       DAY('Table'[start_date])
                     ) 
 &&
 'Date'[Date] <= DATE( YEAR('Table'[end_date]);
                       MONTH('Table'[end_date]) + DATEDIFF('Table'[start_date];'Date'[Date];MONTH);
                       DAY('Table'[start_date]) + DATEDIFF('Table'[start_date];'Table'[end_date];DAY)
                     ) 
)

RETURN
 UNION(never;weekly;biweekly;monthly)

Thank you v-zhenbw-msft for your help.

 

Best,

R.