Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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:
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.
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.
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.
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.
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 If you can paste sample data, relationship between table and the expected output, it will be easier to provide the solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |