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, as well as the related fiscal half, and fiscal year. I am stuck on how to create the measures for a YoY calculation. I want to show the total revenue value in a table or chart for Current Period, Prior Period, and YoY %. All of the example I have seen seem to need an actual date to use DATEDIFF, DATEADD or similar. How can I define Prior Period to align properly to each Current Period given this data? Basic example tables attached. thank you for any assistance!
| DataTable | CalendarTable | ||||||
| Revenue | Period | Period | Start Date | End Date | FY | FH | |
| 15000 | 2017-Q1 | 2017-Q1 | 11/1/2016 | 1/31/2017 | 2017 | 2017-H1 | |
| 1400 | 2017-Q1 | 2017-Q2 | 2/1/2017 | 4/30/2017 | 2017 | 2017-H1 | |
| 20000 | 2017-Q2 | 2017-Q3 | 5/1/2017 | 7/31/2017 | 2017 | 2017-H2 | |
| 2300 | 2017-Q2 | 2017-Q4 | 8/1/2017 | 10/31/2017 | 2017 | 2017-H2 | |
| 18000 | 2017-Q3 | 2018-Q1 | 11/1/2017 | 1/31/2018 | 2018 | 2018-H1 | |
| 1700 | 2017-Q3 | 2018-Q2 | 2/1/2018 | 4/30/2018 | 2018 | 2018-H1 | |
| 16000 | 2017-Q4 | 2018-Q3 | 5/1/2018 | 7/31/2018 | 2018 | 2018-H2 | |
| 1655 | 2017-Q4 | 2018-Q4 | 8/1/2018 | 10/31/2018 | 2018 | 2018-H2 | |
| 14000 | 2018-Q1 | ||||||
| 1800 | 2018-Q1 | ||||||
| 25000 | 2018-Q2 | ||||||
| 2110 | 2018-Q2 | ||||||
| 17000 | 2018-Q3 | ||||||
| 1600 | 2018-Q3 | ||||||
| 16000 | 2018-Q4 | ||||||
| 2100 | 2018-Q4 |
4 Replies
lmondavi try something like this. Just try writing the formula instead of copy pasting as I might have missed out quotes.
CALCULATE(SUM(DataTable[Revenue]), FILTER(CalendarTable, CalendarTable[Start Date] >= DATEADD(CalendarTable[Start Date],-1,Year) && CalendarTable[End Date] < DATEADD(CalendarTable[End Date],-1,Year)))
Unfortunately that didn't work, thanks for the reply though
v-juanli-msftCommunity Support
Hi 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
