Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lizrowden
Helper I
Helper I

TOTALYTD Not working with Month Name

I am trying to work with TOTALYTD and can get it to work when I use Month Number from my Date table, but not with Month Name. Here is the background:

1.) I have a measure from my CFM table which looks like this:

# of Repair Calls = CALCULATE(DISTINCTCOUNT('CFM'[ServiceCallId]), Filter('CFM','CFM'[Partner Call Type] = "REPAIR"))
 

2.) I have the CFM table joined to a date table on the "DateAsInteger" field. This is my date table

Date Service =
ADDCOLUMNS (
CALENDAR (DATE(2009,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort",FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"Week Number",WEEKNUM([Date],1)
)
 
I tried to use the formula, but was unable to get the correct YTD number by month
YTD Repair Calls = TOTALYTD([# of Repair Calls],'Date Service'[Last Service Date])
 
If I use this formula, it works when I use Month Number in my visual, but not when I use Month Name.
YTD Repair Calls = TOTALYTD([# of Repair Calls],'Date Service'[Last Service Date], ALL('Date Service'[Month Number]))
 
See below:
TOTALYTD.PNG

Any help would be greatly appreciated.

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Did you mark your date table as such?

How to Mark as Date Table.png

View solution in original post

That is my problem. I created this date table so long ago and I never marked it as a date table. Thank you all very much

View solution in original post

5 REPLIES 5
lizrowden
Helper I
Helper I

I renamed the column the was originally built as "Date" to "Last Service Date" using the 'Rename' option in the Fields pane in Power BI. Could that be causing it not to work?

@lizrowden that shouldn't cause any issue. In my opinion it shoud work. Send me screeen shot of what you are seeing and not working. Also if possible share sample data/pbix file using onedrive/google drive.



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.

Anonymous
Not applicable

Did you mark your date table as such?

How to Mark as Date Table.png

That is my problem. I created this date table so long ago and I never marked it as a date table. Thank you all very much

parry2k
Super User
Super User

@lizrowden this should work, not sure where you are getting Last Service Data column

 

YTD Repair Calls = TOTALYTD([# of Repair Calls],'Date Service'[Date])


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.