Repeat same year of data for comparison

[dapperscavenger]

I have been asked to provide something really tricky and it's driving me crazy

I have a report that happily compares this year vs last year, next year versus this year, next year versus 2 years ago.  You name it, it does it!  Great!

Now I have been asked to be able to show a comparison specifically against 2019.   This year vs 2019.  Next year vs 2019.  Last year vs 2019.

In the same chart.  So if I have a line chart showing all the actual sales values over 4 years, there should be another line showing the 2019 data, repeating, for comparison.  

How can I create a measure that will repeat the monthly 2019 data over and over no matter what the actual year is?  e.g. January of any year shows Jan 2019 data. 

I have my values in one table with a date, and I have a separate date table, with a relationship on the date.

[v-kkf-msft · Accepted solution]

Hi @dapperscavenger ,

Try the following formula:

2019Year = 
var result =
CALCULATE (
    SUM ( 'Table'[value] ),
    ALL ( Dates ),
    FILTER (
        ALL ( 'Table'[date] ),
        MONTH ( 'Table'[date] ) = MONTH ( MAX ( Dates[Date] ) )
            && YEAR ( 'Table'[date] ) = 2019
    )
)
return 
IF( 
    SUM ( 'Table'[value] ) <> BLANK(),
    result
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[dapperscavenger]

This works beautifully, thank you.

One more question if you have time. Is there anyway to change the calendar year filter to a fiscal year?

FILTER (

ALL ( DimDate[Date] ),

MONTH ( DimDate[Date] ) = MONTH ( MAX ( DimDate[Date] ) )

&& DimDate[FY] = F19

Our Fiscal year ends 30th June .

[v-kkf-msft]

Hi @dapperscavenger ,

I create the following Fiscal Date.

Then create the measure:

2019FiscalYear = 
var result =
CALCULATE (
    SUM ( 'Table'[value] ),
    ALL ( Dates ),
    FILTER (
        ALL ( 'Dates' ),
        MONTH ( 'Dates'[date] ) = MONTH ( MAX ( Dates[Date] ) )
            && Dates[Fiscal Year] = 2019 
    )
)
return 
IF( 
    SUM ( 'Table'[value] ) <> BLANK(),
    result
)

Best Regards,
Winniz

[PaulDBrown]

Try:

2019 data = CALCULATE([Your measure], YEAR(Date table [date])= 2019)