Forum Discussion

cptjamesmisson's avatar
cptjamesmisson
Frequent Visitor
6 years ago

Exercise to date Last year incorrectly adds future values

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 c...
Need Help
v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
cptjamesmisson
Frequent Visitor
6 years ago

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.