Forum Discussion
Year over Year with tricky dates
Hi, I have data with fiscal quarter (text field) as the lowest aggregation level on each transaction. I have a calendar table that includes a related Start Date and End Date for each fiscal quarter, ...
v-juanli-msft
Community Support
Community SupportHi lmondavi
After testing with any DATETIME function, like DATEDIFF, DATEADD or similar, it is not possible to get results as you expected.
Here is a workaround.
Assume "Prior Period" here refers to the same quarter of the last year.
1.create relationship between two tables
2. create measures columns in DataTable
Measure
Current Period = CALCULATE(SUM('DataTable'[Revenue]),ALLEXCEPT('DataTable','DataTable'[Period]))
Columns
date-start = RELATED(CalendarTable[Start Date]) Prior Period =
CALCULATE (
SUM ( 'DataTable'[Revenue] ),
FILTER (
ALL ( 'DataTable' ),
MONTH ( [date-start] ) = MONTH ( EARLIER ( 'DataTable'[date-start] ) )
&& YEAR ( [date-start] )
= YEAR ( EARLIER ( 'DataTable'[date-start] ) ) - 1
)
)
Please let me know how to calculate YoY %.
Best Regards
Maggie
Thanks, but I don't think this is flexible enough. I need to bring in other filters, but the 'current period' does not repond to those filters. Also, with this solution I would have to create so many new measures and columns for every value that I need to do a year over year comparison for. thank you for taking time to answer, I do appreciate it