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 Proposed Project Costs],
   CalendarSubmitted[Date],
   CalendarSubmitted[Date]<=Today(),"6/30")

I made my chart axis CalendarSubmitted[Date], but the chart shows everything from the beginning of the fact table. I want it to just show this year by date. I don't want to have to manually change the filter on the graph every time the FY changes. How do I incorportate that into the measure? And I ultimately want to add another line with a Last YTD measure.

 

I

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community 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
        Community 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.