Reply
poweringthru
Helper I
Helper I
Partially syndicated - Outbound

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:

DateYearMonthNumMonthWeekdayDayWeekDayOfYear
Sunday, January 1, 202320231jan6111
Monday, January 2, 202320231jan0222
Tuesday, January 3, 202320231jan1323
...       

 

Calendar = calendar(date(2023,1,1),date(2024,12,31))
Year = YEAR('Calendar'[Date])
MonthNum = MONTH('Calendar'[Date])
Month = FORMAT('Calendar'[Date],"mmm")
Weekday 
= WEEKDAY(Calendar[Date],3) /* option 3 gives 0 = Monday, 1 = Tuesday, ...
Day = DAY(Calendar[Date])
Week = WEEKNUM('Calendar'[Date],2)
DayOfYear = FORMAT([Date], "Y")

Plan assigns an Amount for an Id on a given Weekday:
IdAmountWeekday
110
111
112
113
114
115
116
230
231
232
233
234
235
236
380
382
384
460
462
464
5115
5116
691
693

 

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:

poweringthru_0-1683042483555.png

How would you go about getting it right also on a monthly and yearly basis?

Do I need to adjust TotalAmount measure?

2 REPLIES 2
amitchandak
Super User
Super User

Syndicated - Outbound

@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.

 

Join us as experts from around the world come together to shape the future of data and AI!
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

Syndicated - Outbound

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:

https://we.tl/t-2kioURKTvH

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)