YTD line chart to be stopped at today's date

[nervida]

Hi Everyone, 

I would appreaciate if anyone can help me with building a line chart for YTD data but the line to be intercepted (stopped) at today's date (or lattest date in the data). Let me kindly explain further: when I built the visualization, the chart shows me the line till the end of August, despite we are in the middle in the month, I want the line to end at my blue drawing, not at the yellow as it does. 

 

I have used this calendar table:

Date Table = ADDCOLUMNS(CALENDAR (DATE(2000,1,1), TODAY()))

And this formula: 

Yr cumulative data =
IF(ISFILTERED('Date Table'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),TOTALYTD(SUM('UK List'[GrossAmount]),'Date Table'[Date]))

Even when I tried the monthly revenue line chart, with the following formula is gives the same feature, by :

YTD cummulative =
VAR RetVal = TOTALYTD(SUM('UK List'[GrossAmount]),'Date KPI'[Date].[Date])
RETURN
IF(MAX('Date Table'[Date])<=TODAY(),RetVal)

I would be very grateful if you can help me find this solution, as it will help me to represent the real picture of the month.

Thank you so much, 

All the best, 

Nervida

[Anonymous]

You can use the "Filter on this Visual" section to drive the results you want. Simily apply the filter "Is Not Blank" to your metric, and you should see the results you want. 

[v-cherch-msft]

Hi @nervida

You may try to make the date hierarchy as below picture.

Regards,

Cherie

[nervida]

Hi Cherie, 

Thank you so much for your response. When I chose your given options, it stops exactly to the today's date, but it gives me in the axis the daily number and it is a very long table so I need to scroll into the graph to see all the yearly picture. Do you know any way of formating the axis so that only the months are shown: 

Thank you so much, 

Nervida

[v-cherch-msft]

Hi @nervida

You may try to use date as Axis.

Regards,

Cherie

[Seward12533]

Try this
YTD cummulative =
VAR RetVal = TOTALYTD(SUM('UK List'[GrossAmount]),'Date KPI'[Date].[Date])
RETURN
IF(MAX('Date Table'[Date])<DATE(YEAR(TODAY()),MONTH(TODAY(),1)),RetVal)

[nervida]

Hi Seward, 

Thank you so much for your response, however the formula that you gave it to me seems to cut the August totally. It ends up until July. I want to it to shows me also the August data up to today's date. 

All the best, 

Nervida

[Seward12533]

Sorry that is what I thought you wanted. Something closer to your original formula should work but I’m guessing your data ends before today so there are still days between the last date with data and today.

So rather than today you need to use something like MAX of the date the measure your plotting has data rather than TODAY.

[nervida]

Hi Seward, 

Dont worry at all, indeed I need to thank you for your support. 

I checked the data and there are today's data value in it. Even when I put the week as axis it gives the straight line for all that week. May be I am missing something on visualization? Does your data end exactly to the last date given?

Thank you again, 

Have a nice day, 

Nervida