Forum Discussion

rdorsey's avatar
rdorsey
Frequent Visitor
5 years ago
Solved

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: 

CALCULATE(COUNTA('Shipped'[Id]), YEAR('Shipped'[Date]) = YEAR(TODAY())-1)
This Year:
CALCULATE(COUNTA('Shipped'[Id]), YEAR('Shipped'[Date]) = YEAR(TODAY()))
YTD Last Year:
CALCULATE(TOTALYTD(COUNTA('Shipped'[Id]), 'Shipped'[Date].[Date]),YEAR('Shipped'[Date]) = YEAR(TODAY())-1)
YTD This Year:
CALCULATE(TOTALYTD(COUNTA('Shipped'[Id]), 'Shipped'[Date].[Date]),YEAR('Shipped'[Date]) = YEAR(TODAY()))
 
Ive also attached a picture of some visuals and table illustrating the issue:
 
Basically I just need the visual on the left to also have the orange line from the visual on the right.
 
Any help would be greatly appreciated!
  • 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's avatar
    aj1973
    Icon for Community Champion rankCommunity 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))