Forum Discussion

dapperscavenger's avatar
dapperscavenger
Icon for Helper V rankHelper V
5 years ago
Solved

Repeat same year of data for comparison

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.

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

4 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      dapperscavenger
      Icon for Helper V rankHelper V
      5 years ago

      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's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Try:

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