Forum Discussion
Need help
Hi, I am trying to report on comparing record counts between current and past years. I tried the samelastyearperiod function and the dateadd function, but I haven't had any luck. Can you please help me? Thank you.
I created a fiscal year (starting from 1 April) calendar table (Date) that contains just dates, and I have a fact table that contains record ID and file-received dates.
I created the following two measures.
Count_recordID = count(recordID)
Previous year count = calculate(count_recordID,samelastyearperiod(Date_table[date]).
Inactivated the relationship between date table and fact table.
I am not getting desired output. Someone can help. Thank you.
Reactivate the relationship between your Date table and the fact table on the date fields. This is necessary for the SAMEPERIODLASTYEAR or DATEADD functions to work properly.
Count_recordID = COUNT('FactTable'[recordID])PreviousYearCount =
CALCULATE(
[Count_recordID],
SAMEPERIODLASTYEAR('Date_table'[date])
)Place Year from the Date table in the Rows of a table visual.
Add Count_recordID and PreviousYearCount measures to Values.If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn
2 Replies
Reactivate the relationship between your Date table and the fact table on the date fields. This is necessary for the SAMEPERIODLASTYEAR or DATEADD functions to work properly.
Count_recordID = COUNT('FactTable'[recordID])PreviousYearCount =
CALCULATE(
[Count_recordID],
SAMEPERIODLASTYEAR('Date_table'[date])
)Place Year from the Date table in the Rows of a table visual.
Add Count_recordID and PreviousYearCount measures to Values.If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedInThank you. It works.
