Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

YTD Running Total Chart

I am trying to create a chart showing the running cummulative total by day for the current fiscal year. Here is the measure I am using:   Total Proposed Project Costs YTD = TOTALYTD( [Total Prop...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous 

Would you like to show total Ytd values for current year
(eg, this year2019, show Ytd values from 2019/1/1 to today)

Measure = TOTALYTD([sum cost],'calendar'[Date],'calendar'[Date]<=TODAY()&&YEAR('calendar'[Date])=YEAR(TODAY()))

Or for current year 2019, show values from 2018/7/1 to 2019/6/30?

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Closer to the latter. For the current year (2019), I want to show FY 2020, which runs 7/1/2019 to 6/30/2020. I also want to show Last YTD on the same graph as a different line. My CalendarSubmit table has a column for FY, so:

     

    Date                      FY

    8/1/2019               2020

    5/1/2019               2019

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous 

      Create two measures

      CY_YTD-TODAY =
CALCULATE (
    TOTALYTD (
        [sum cost],
        'calendar'[Date],
        'calendar'[fiscal year]
            = YEAR ( TODAY () ) + 1,
        "6/30"
    ),
    FILTER ( 'calendar', 'calendar'[Date] <= TODAY () )
)

LY_YTD = TOTALYTD([sum cost],'calendar'[Date],'calendar'[fiscal year]=YEAR(TODAY()),"6/30")
      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I'm getting closer, but it's still not right.

         

         

         

         

        I'd like both lines to start on the graph in July 2019, so I can direclty see how this year is doing compared to last year by the same point in the year.

         

        I set the filter on the graph to relative date filtering for dates in this year, but that leaves the tail on the left. I want the graph to start on July 1 of whatever current fiscal year we are in (currently 2020).

         

        Thanks!