Forum Discussion
lizrowden
Helper I
Helper ITOTALYTD 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:
Any help would be greatly appreciated.
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
5 Replies
lizrowdenI 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?
