Forum Discussion
How do I get two YTD lines on a single year plot?
I'm trying to compare incoming items from this year and last year. The visual I've been asked to provide is a line and clustered column chart where the X axis is "Month" and the Y is "Items". The columns need to be the total items for each month and the lines should be a YTD sum of the items. I have successfully done all of this except for getting both of the lines to show up on a single 12 month scale, I can only get the current year's YTD data to show up on the plot. I think my issue has something to do with how the Date hierarchy is interacting with things, but I can't figure out much past that.
If it helps, here's the DAX for the measures I'm using:
Last Year:
YTD Last Year:
YTD This Year:
CALCULATE(TOTALYTD(COUNTA('Shipped'[Id]), 'Shipped'[Date].[Date]),YEAR('Shipped'[Date]) = YEAR(TODAY()))
Hi rdorsey
To use Time Intelligence Functions you need to add a Calendar table to your model and relate it to the "Shipped" table @ the DATE column. Then you will use DATEADD to get YTD_LastYear. like here
YTD__LastYear = CALCULATE([Sales] , DATEADD('Calendar'[Date] , -1, MONTH))YTD__ = CALCULATE([Sales] , DATEADD('Calendar'[Date], 0 , MONTH))
1 Reply
- aj1973
Community Champion
Hi rdorsey
To use Time Intelligence Functions you need to add a Calendar table to your model and relate it to the "Shipped" table @ the DATE column. Then you will use DATEADD to get YTD_LastYear. like here
YTD__LastYear = CALCULATE([Sales] , DATEADD('Calendar'[Date] , -1, MONTH))YTD__ = CALCULATE([Sales] , DATEADD('Calendar'[Date], 0 , MONTH))