SAMEPERIODLASTYEAR returning blank / 0

Question

I'm creating a calculated table in DAX and trying to come up with a total credits for last year measure. The strange thing is this was a dashboard I created in 2017 and didn't refresh until recently. Upon trying to refresh there were issues and I had to rebuild the dashboard but I took great care to recreate the same relationships and tables. This calculation was working before and isn't now. Further, I have other calculations using SAMEPERIODLASTYEAR that work just fine but they do reference other tables.

I did go in and mark the dates table as a date table as I saw that might be the issue but it didn't resolve anything. I've tried so many different things with this and nothing is resolving it. Any ideas? Unfortunately this is sensitive data I can't post the file.

Another odd thing about this is that I can compute this as a measure in the Course Information table with no issues. When I try to compute it as a column in that table it gives me a circular error - and I'm not sure that would work anyway because it seems I have to compute each of these metrics for the calculated table to return results correctly (I need this table because I'm using the Power KPI matrix which has to have everything summarized to return results the way I want them).

This is the line I'm having trouble with:

"TotalCreditHoursCountLY", CALCULATE(sum('Course Information'[Program Credit Hours])+0, SAMEPERIODLASTYEAR('Dates'[Date])),

This is the full syntax for the calculated table -

CourseEnrollmentTotals = Summarize ('Course Information',[Area], [Program Title], 'Dates'[JanuaryDate], "Credit Hours",SUM('Course Information'[Program Credit Hours])+0,

"TotalCreditHoursCountLY", CALCULATE(sum('Course Information'[Program Credit Hours])+0, SAMEPERIODLASTYEAR('Dates'[Date])),

"DiffTYLYCreditHours", If(Isblank([TotalCreditHoursCountLY]), BLANK(), (sum('Course Information'[Program Credit Hours]) - [TotalCreditHoursCountLY])),

"YOYChangeCreditHours",If([TotalCreditHoursCountLY]=0,0,(DIVIDE ([Diff. TY and LY Credit Hours],[TotalCreditHoursCountLY]))),

"Completion Count", ('Course Information'[Completion Count]),"CompletionLY", CALCULATE( ('Course Information'[Completion Count]), SAMEPERIODLASTYEAR('Dates'[Date] )),

"DiffTYLYCompletion", If(Isblank([TotalCompletionCountLY]), BLANK(), (sum('Course Information'[Completion.Count]) - [TotalCompletionCountLY])),

"YOYChangeCompletion", (If(([TotalCompletionCountLY]=0),0, ([Diff. TY and LY Completion Count]/[TotalCompletionCountLY]))),

"YOYSatisfactory", 'Course Information'[YOYSatisfactory],

"PrefixTotalHours", sum('All Course Credits Grouped'[Credit Hours for Prefix])+0,

"PrefixCreditHoursLYg", CALCULATE(sum('All Course Credits Grouped'[Credit Hours for Prefix])+0, SAMEPERIODLASTYEAR('Dates'[Date])),

"PrefixCreditHoursDiffg", If(Isblank([PrefixCreditHoursLYg]), BLANK(), ('All Course Credits Grouped'[PrefixTotalHoursg]) - [PrefixCreditHoursLYg]),

"YOYChangePrefixCHg", If([PrefixCreditHoursLY]=0,0,(DIVIDE ([Diff. TY and LY Prefix Credit Hours],[PrefixCreditHoursLY]))),

"Average Instructional Cost",CALCULATE(Average('STDandAverageTable'[Ave Inst. Cost])), "STDInstructCost", CALCULATE(Average('STDandAverageTable'[STDInstructionalCost])),

"NetRevSatisfactory", 'Expense'[NetRevSatisfactory], "Fill Rate Satisfied", 'Course Information'[FillRateSatisfied],

"SuccessRateSatisfactory", 'Course Information'[SuccessRateSatisfactory],

"Completion Target", 'Course Information'[Completion Target],

"TransferSuccess", 'Transfer by Program'[TransferSuccess],

"AccredSat", 'Course Information'[AccredSat],

"Instructional Cost Satisfactory",CALCULATE(Average('STDandAverageTable'[Ave Inst. Cost])), "Course Success",Calculate((sum('Course Information'[Program Success Grade])/(sum('Course Information'[Program Seat Count])))),"SuccessRateNeedsAttn", (CALCULATE('Course Information'[SuccessRateSatisfactory]-.05)))

anonymous · Answer

Hi&nbsp;ahumke&nbsp;,
Please try to update the formula of your calculated table CourseEnrollmentTotals&nbsp;as below, the part with red font is new added:

CourseEnrollmentTotals&nbsp;=SUMMARIZE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;'Course&nbsp;Information',&nbsp;&nbsp;&nbsp;&nbsp;[Area],&nbsp;&nbsp;&nbsp;&nbsp;[Program&nbsp;Title],&nbsp;&nbsp;&nbsp;&nbsp;'Dates'[JanuaryDate],&nbsp;&nbsp;&nbsp;&nbsp;"Credit&nbsp;Hours",&nbsp;SUM&nbsp;(&nbsp;'Course&nbsp;Information'[Program&nbsp;Credit&nbsp;Hours]&nbsp;)&nbsp;+&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;"TotalCreditHoursCountLY",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Course&nbsp;Information'[Program&nbsp;Credit&nbsp;Hours]&nbsp;)&nbsp;+&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SAMEPERIODLASTYEAR&nbsp;(&nbsp;'Dates'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;'Dates'&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"DiffTYLYCreditHours",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK&nbsp;(&nbsp;[TotalCreditHoursCountLY]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;(),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Course&nbsp;Information'[Program&nbsp;Credit&nbsp;Hours]&nbsp;)&nbsp;-&nbsp;[TotalCreditHoursCountLY]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"YOYChangeCreditHours",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[TotalCreditHoursCountLY]&nbsp;=&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;DIVIDE&nbsp;(&nbsp;[Diff.&nbsp;TY&nbsp;and&nbsp;LY&nbsp;Credit&nbsp;Hours],&nbsp;[TotalCreditHoursCountLY]&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Completion&nbsp;Count",&nbsp;(&nbsp;'Course&nbsp;Information'[Completion&nbsp;Count]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"CompletionLY",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;'Course&nbsp;Information'[Completion&nbsp;Count]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SAMEPERIODLASTYEAR&nbsp;(&nbsp;'Dates'[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"DiffTYLYCompletion",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK&nbsp;(&nbsp;[TotalCompletionCountLY]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;(),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;SUM&nbsp;(&nbsp;'Course&nbsp;Information'[Completion.Count]&nbsp;)&nbsp;-&nbsp;[TotalCompletionCountLY]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"YOYChangeCompletion",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;[TotalCompletionCountLY]&nbsp;=&nbsp;0&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;[Diff.&nbsp;TY&nbsp;and&nbsp;LY&nbsp;Completion&nbsp;Count]&nbsp;/&nbsp;[TotalCompletionCountLY]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"YOYSatisfactory",&nbsp;'Course&nbsp;Information'[YOYSatisfactory],&nbsp;&nbsp;&nbsp;&nbsp;"PrefixTotalHours",&nbsp;SUM&nbsp;(&nbsp;'All&nbsp;Course&nbsp;Credits&nbsp;Grouped'[Credit&nbsp;Hours&nbsp;for&nbsp;Prefix]&nbsp;)&nbsp;+&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;"PrefixCreditHoursLYg",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'All&nbsp;Course&nbsp;Credits&nbsp;Grouped'[Credit&nbsp;Hours&nbsp;for&nbsp;Prefix]&nbsp;)&nbsp;+&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SAMEPERIODLASTYEAR&nbsp;(&nbsp;'Dates'[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"PrefixCreditHoursDiffg",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK&nbsp;(&nbsp;[PrefixCreditHoursLYg]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;(),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;'All&nbsp;Course&nbsp;Credits&nbsp;Grouped'[PrefixTotalHoursg]&nbsp;)&nbsp;-&nbsp;[PrefixCreditHoursLYg]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"YOYChangePrefixCHg",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[PrefixCreditHoursLY]&nbsp;=&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;DIVIDE&nbsp;(&nbsp;[Diff.&nbsp;TY&nbsp;and&nbsp;LY&nbsp;Prefix&nbsp;Credit&nbsp;Hours],&nbsp;[PrefixCreditHoursLY]&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Average&nbsp;Instructional&nbsp;Cost",&nbsp;CALCULATE&nbsp;(&nbsp;AVERAGE&nbsp;(&nbsp;'STDandAverageTable'[Ave&nbsp;Inst.&nbsp;Cost]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"STDInstructCost",&nbsp;CALCULATE&nbsp;(&nbsp;AVERAGE&nbsp;(&nbsp;'STDandAverageTable'[STDInstructionalCost]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"NetRevSatisfactory",&nbsp;'Expense'[NetRevSatisfactory],&nbsp;&nbsp;&nbsp;&nbsp;"Fill&nbsp;Rate&nbsp;Satisfied",&nbsp;'Course&nbsp;Information'[FillRateSatisfied],&nbsp;&nbsp;&nbsp;&nbsp;"SuccessRateSatisfactory",&nbsp;'Course&nbsp;Information'[SuccessRateSatisfactory],&nbsp;&nbsp;&nbsp;&nbsp;"Completion&nbsp;Target",&nbsp;'Course&nbsp;Information'[Completion&nbsp;Target],&nbsp;&nbsp;&nbsp;&nbsp;"TransferSuccess",&nbsp;'Transfer&nbsp;by&nbsp;Program'[TransferSuccess],&nbsp;&nbsp;&nbsp;&nbsp;"AccredSat",&nbsp;'Course&nbsp;Information'[AccredSat],&nbsp;&nbsp;&nbsp;&nbsp;"Instructional&nbsp;Cost&nbsp;Satisfactory",&nbsp;CALCULATE&nbsp;(&nbsp;AVERAGE&nbsp;(&nbsp;'STDandAverageTable'[Ave&nbsp;Inst.&nbsp;Cost]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Course&nbsp;Success",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Course&nbsp;Information'[Program&nbsp;Success&nbsp;Grade]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;/&nbsp;(&nbsp;SUM&nbsp;(&nbsp;'Course&nbsp;Information'[Program&nbsp;Seat&nbsp;Count]&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"SuccessRateNeedsAttn",&nbsp;(&nbsp;CALCULATE&nbsp;(&nbsp;'Course&nbsp;Information'[SuccessRateSatisfactory]&nbsp;-&nbsp;.05&nbsp;)&nbsp;))

If the above one is not working for your scenario, please check the solution in the following similar threads. Hope they can help you resolve the problem.
SUMMARIZE function and SAMEPERIODLASTYEAR returning BLANK
SAMEPERIODLASTYEAR encapsulated into a SUMX/SUMMARIZE

Best Regards

Forum Discussion

SAMEPERIODLASTYEAR returning blank / 0

1 Reply

Related content

SAMEPERIODLASTYEAR returns Blank

SAMEPERIODLASTYEAR is blank

Sameperiodlastyear function returning "Blank"

DateAdd and SamePeriodLastYear Returning Blank Values

Measure is returning BLANK