cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rylach
Helper II
Helper II

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), 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.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.
1 ACCEPTED SOLUTION

Hi @rylach ,

 

We can use the following steps to meet your requirement.

 

1. Create a date table first.

 

Date = CALENDARAUTO()

 

C 1.jpg

 

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

 

c 2.jpg

 

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" ) )

 

C 3.jpg

 

C 4.jpg

 

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.

View solution in original post

8 REPLIES 8
v-zhenbw-msft
Community Support
Community Support

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.

Hi,

Unfortunately, this is not that simple 😉

 

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

itemstart_dateend_dateinclude_holidaysrecurringrecurring periodrecurring_end_dateamount per day
shoes2020-06-222020-06-24falseYESWEEK2020-07-0710

 

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.

 

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.

Hi @rylach ,

 

We can use the following steps to meet your requirement.

 

1. Create a date table first.

 

Date = CALENDARAUTO()

 

C 1.jpg

 

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

 

c 2.jpg

 

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" ) )

 

C 3.jpg

 

C 4.jpg

 

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.

View solution in original post

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.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.

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.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.

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.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.

Brilliant! Thank you.
"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.
parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors