Forum Discussion

Helper 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

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

Helper II

6 years ago

Hi,

Unfortunately, this is not that simple 😉

Look at the example record in the 'amounts' table:

item	start_date	end_date	include_holidays	recurring	recurring period	recurring_end_date	amount per day
shoes	2020-06-22	2020-06-24	false	YES	WEEK	2020-07-07	10

This record expands to quantities:

First period:

22.06 - 10

23.06 - 10

24.06 - 10

Second period (because we have weekly reccurence AND week after start_date is before recurring_end_date)

29.06 - 10

30.06 - 10

01.07 - 10

3rd period (because we have weekly reccurence AND another week after start_date is before recurring_end_date)

06.07 - 10

07.07 - 10

08.07 - 10

So, the measure should return, when used with monthly aggregation:

June - 50

July - 40

I hope I explained it well 😉

I could do this by:
1. crossjoin with calendar table (multiplies this record for every day)

2. calculatetable, filters - filtering out days outside of ranges indicated by date columns.

Does it make sense or there is some better approach?

Best,

v-zhenbw-msft

Community 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
Helper II
6 years ago
Brilliant! Thank you.
rylach
Helper II
6 years ago
Hi,
Actually the solution misses one detail i.e. recurring_end_date.

If we set it somewhere far in the future (e.g. 01/01/2021) then the measure still shows only three periods. And should show as many periods as needed, repeated every week, as long the start_date of the last period is before recurring_end_date.

Is it possible to calculate it recursively?

Best,
R.

rylach

Helper II

6 years ago

Hi v-zhenbw-msft

I suppose I have a solution.

Look at the following definition of Table 3:

Table 3 = 

VAR x =

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

 'Table'[item];
 [Date];
 'Table'[start_date];
 'Table'[end_date];
 'Table'[amount per day]
 )

VAR y = ADDCOLUMNS(x;

 "part_of_period";
 IF (
     '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)
    ; TRUE()
    ; FALSE()
 )

 ) 

RETURN
  y

It seems such a column (instead of calculated column added to the 'Table' definition) makes it possible to filter all days of all possible periods starting between start_date and recurring_end_date.

I'll let you know when I finish it.

Best,

rylach

Helper 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,

Forum Discussion

Calculating amount given for period - from one row

Related content

Dynamic Calculation Different Period

Calculate bonus amount for retroactive months.

Calculation of Total Amount With Sales Entry

Dax calculate total amount ignoring filter

Calculate period over period