Exercise to date Last year incorrectly adds future values

[cptjamesmisson]

I have entered a year-to-date cumulative sum measure (FYTD) for the current year and another measure for the same period last year (FYTDLY). My end of the fiscal year is 6/30. My data set for the current FYTD runs up to 4/17/20, so my FYTDLY should only run through 4/17/19 as well, matching the two periods correctly and excluding any future data from 4/18/19-6/30/19.

Graphically, this is the case - FYTDLY only plots the data through 4/17/19 instead of incorrectly plotting the data until the end of fiscal year 6/30/19.

They look good in a table, showing the correct execution totals in both years in terms of quality with data displayed through 4/17. However, the table incorrectly sums FYTDLY - the total execution from the last data point is $121 K (until the correct cut-off date 4/17/19) while the table shows a sum of $145K, which is the total sum until the end of fiscal year 6/30/19.

How do I get the table to correctly add FYTDLY through only 4/17/19 and exclude any future value through 6/30/19 that behaves delta b/t $145K and $121K?

Here's my existing code for both meaures, and thank you in advance:

FYTD - $ Def = 
    var lastprodate = maxx(all(PRODUCTION[date]),PRODUCTION[date])
    var fytd = CALCULATE([$ Def], DATESYTD(DATES[Date],"30/6"))
return 
    IF(MIN(DATES[Date])<=lastprodate,fytd,blank())

FYTDLY - $ Def = 

var lastprodate = maxx(all(PRODUCTION[date]),PRODUCTION[date])
    var fytdly = CALCULATE([FYTD - $ Def],SAMEPERIODLASTYEAR(DATES[Date]))

return 
    IF(MIN(DATES[Date])<=lastprodate,fytdly,blank())

[v-easonf-msft]

Hi , @cptjamesmisson 

You can try to nest an “isinscope” function outside the original measure.Something like the following：

Measure 2 =
VAR a =
    MAX ( PRODUCTION[date] )
VAR b =
    CALCULATE (
        [$ Def],
        FILTER (
            PRODUCTION,
            PRODUCTION[date]
                >= DATE ( YEAR ( a ) - 2, 7, 1 )
                && PRODUCTION[date]
                    <= DATE ( YEAR ( a ) - 1, MONTH ( a ), DAY ( a ) )
        )
    )
RETURN
    IF ( ISINSCOPE ( 'DATES'[Date] ), [FYTDLY - $ Def], b )

Here is a demo.

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cptjamesmisson]

I've implemented your 'Measure 2'.  It looks like it correctly sums the running total as it matches the FYTDLY column, but per the red shade, it doesn't include the final number - $121K - as a total at the bottom of the table.

Also, if I drop 'Measure 2' that you created into a card, it should populate with the $121K number, but it populates a blank:

Any sense as to why that total isn't there in mine whereas it is in your sample file?

Also, per the red shade in your version below, the total is now missing from the the FYTD and FYTDLY columns.  What happened to that total?

Thanks again for the help and prompt reply.