- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate Sum on a Weekday basis fails on a Monthly and Yearly display
I'm using 2 Tables for this, Calendar and Plan.
Calendar has the usual:
Date | Year | MonthNum | Month | Weekday | Day | Week | DayOfYear |
Sunday, January 1, 2023 | 2023 | 1 | jan | 6 | 1 | 1 | 1 |
Monday, January 2, 2023 | 2023 | 1 | jan | 0 | 2 | 2 | 2 |
Tuesday, January 3, 2023 | 2023 | 1 | jan | 1 | 3 | 2 | 3 |
... |
Weekday = WEEKDAY(Calendar[Date],3) /* option 3 gives 0 = Monday, 1 = Tuesday, ...
Plan assigns an Amount for an Id on a given Weekday:
Id | Amount | Weekday |
1 | 1 | 0 |
1 | 1 | 1 |
1 | 1 | 2 |
1 | 1 | 3 |
1 | 1 | 4 |
1 | 1 | 5 |
1 | 1 | 6 |
2 | 3 | 0 |
2 | 3 | 1 |
2 | 3 | 2 |
2 | 3 | 3 |
2 | 3 | 4 |
2 | 3 | 5 |
2 | 3 | 6 |
3 | 8 | 0 |
3 | 8 | 2 |
3 | 8 | 4 |
4 | 6 | 0 |
4 | 6 | 2 |
4 | 6 | 4 |
5 | 11 | 5 |
5 | 11 | 6 |
6 | 9 | 1 |
6 | 9 | 3 |
The relationship I'm using between tables for now is Weekday - Weekday (a many to many, as you can imagine).
The measure for now is:
TotalAmount = CALCULATE(SUM(Plan[Amount]))
So, the thing is, if I display a Matrix for TotalAmount by Id against Date works fine on a daily or weekly basis but fails for month and year totals, it looks as following:
How would you go about getting it right also on a monthly and yearly basis?
Do I need to adjust TotalAmount measure?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@poweringthru , Does your second table has a date ? why do you need populate weekday to ID?
can you make sure your date to join(calendar date and fact date) is correct? Because if the data is correct at the date of the calendar, it should rollup correctly
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey, thank you for answering!
Plan table is Weekday driven, rows don't have a specific date. I need it to apply to any given month and year.
Here is a test pbix document in a WeTransfer link:
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
79 | |
59 | |
58 | |
44 |
User | Count |
---|---|
179 | |
120 | |
82 | |
70 | |
53 |