Line graph with cut off point on TODAY()

[Patryk_PL]

Hi!

I'm working on a dashboard to show materials scrapped in current year YTD while trynig to simoultanously show previous year. I have created two measures:

ScrapLastYear = CALCULATE(SUM(Data[Amt.in loc.cur.]),SAMEPERIODLASTYEAR(Data[Posting Date].[Date])) to show whole previous year and second:

SumEndDayToday = CALCULATE(SUM(Data[Amt.in loc.cur.]),DATESYTD(Data[Posting Date]),MAX(Data[Posting Date],TODAY()))

The result is almost okay, but I can't figure out, what should be changed to show current year only up to end of data set (currently - beginning of July). Line graph is continuing the line in the future:

What am I doing wrong here?

[v-deddai1-msft · Accepted solution]

Hi @Patryk_PL ,

Did you have a dim_date table in your date model. I suggest you create one and you can use the following measure:

ScrapLastYear =
VAR A =
    CALCULATE ( MAX ( Data[Posting Date] ), ALL ( Data ) )
RETURN
    IF (
        MAX ( Dim_Date[Date] ) <= A,
        CALCULATE (
            SUM ( Data[Amt.in loc.cur.] ),
            SAMEPERIODLASTYEAR ( Dim_Date[Date] )
        )
    )

SumEndDayToday =
VAR A =
    CALCULATE ( MAX ( Data[Posting Date] ), ALL ( Data ) )
RETURN
    IF (
        MAX ( Dim_Date[Date] ) <= A,
        CALCULATE ( SUM ( Data[Amt.in loc.cur.] ), DATESYTD ( Dim_Date[Date] ) )
    )

Use the date column from the dim_date table as x-axis.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Patryk_PL]

Ok, it looks better when I've created separate Date table (from https://www.youtube.com/watch?v=F-GGF9OS5ys this video). Now I have something like this:

What else shall be changed? I've created measures like mentioned, but...

[Patryk_PL]

Ok, I found the issue - just entered a filter for current year and worked fine now.

[amitchandak]

@Patryk_PL , to me this does not seem like YTD

second, you should use only the date table

examples

YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))

YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Same <= _max can use with a trailing measure or measure sameperiodlastyear

example

if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),SAMEPERIODLASTYEAR('Date'[Date])), blank())

if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),dateadd('Date'[Date],-1,year), blank())