Calculate using PriorYear but dont display future

[nev]

I have a chart that displays monthly values, then a comparison bar from the same period last year,

It uses the following formula to get the prior year:

Prior Year Revenue = CALCULATE(SUM('V_AU ALR Cost and Qty'[Amount (LCY)])*-1, SAMEPERIODLASTYEAR('Gen Date Table'[Date]))

Now the problem is that it is displaying this year's values in next year, so even though we are in 2017, it is displaying this year's values in 2018 (In preparation for them being the prior year to that year's actuals)

I want to remove these values, everything greater than this month would be great, as the first stepI have tried to remove them by adding a filter to my calculate, but it has no effect at all, I get the same result as the original above:

Prior Year Revenue = CALCULATE(SUM('V_AU ALR Cost and Qty'[Amount (LCY)])*-1, SAMEPERIODLASTYEAR('Gen Date Table'[Date]), 'Gen Date Table'[Date]<= TODAY())

Any Ideas?

[v-yulgu-msft]

Hi @nev,

What is the unexpected result you get? I cannot image what did you mean "it is displaying this year's values in next year, so even though we are in 2017, it is displaying this year's values in 2018". Please share your sample data and screenshot of current output to elaborate your scenario.

Below is the my test, hope it helpful to your scenario.

Data view and relationship between source table and date table.

Measures.

Current year =
CALCULATE (
    SUM ( 'V_AU ALR Cost and Qty'[Amount (LCY)] ),
    'V_AU ALR Cost and Qty'[Year] = YEAR ( TODAY () )
)

Prior Year Revenue =
CALCULATE (
    SUM ( 'V_AU ALR Cost and Qty'[Amount (LCY)] ),
    SAMEPERIODLASTYEAR ( 'Gen Date Table'[Date] )
)

Then, in chart visual:

Axis - enter Date hierarchy  from date table (in your scenario, it's 'Gen Date Table')

Value - enter the above two measure: [Current year] and [Prior Year Revenue]

Best regards,
Yuliana Gu

[nev]

Thanks for the response, the image of the problem chart is below, in the picture the normal sum is the light colour, and the dark colour is the formula for getting the prior year value.

As you can see the current year has both the value and the prior year value, but beyond today, the "Prior Year" values are still showing up, I want to filter this chart so that no values are shown for next month onwards.

Hope this makes sense?

https://1drv.ms/i/s!AsT0oct8oHb9gvwP2bmPaJsLRimakw

[v-yulgu-msft]

Hi @nev,

What is your sample data?

Regards,

Yuliana Gu

[nev]

Sorry, I don't have sample data, only production data from their live system which I cannot share.