Calculating amount given for period - from one row

[rylach]

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), calendar with holidays marked in a separate table

recurring (true/false)

recuring period (week, two weeks, month)

recurring end date (last date of start_date if recurring).

amount_per_day - the value of items for each day between start and end_dates, with the condition for holiday

My goal is to create a measure, let's say "Sum of amount", which could be used in calendar-based visuals and aggregated per month, quarters etc.. It should correctly calculate sums for periods spanning through the month boundaries.

How would you create such a measure? I thought about a crossjoin of such a table with the calendar table and then operating with

calculate and filters, but maybe it isn't the only/best approach?

Best,

R.

[v-zhenbw-msft]

Hi @rylach ,

We suggest you can create a month column and quarter column, then add two slicers to use them.

And put the following measure to your context.

SUM_ =
CALCULATE ( SUM ( 'Table'[amount_per_day] ) )

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

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]

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,

R.

[v-zhenbw-msft · Accepted solution]

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]

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.

[rylach]

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,

R.

[rylach]

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]

Brilliant! Thank you.

[parry2k]

@rylach If you can paste sample data, relationship between table and the expected output, it will be easier to provide the solution.