Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Compare last year vs current year (months) on same visual

Hey everyone! I could use some guidance on how to create a visual that is able to show each month (last year vs current year) side by side on a stacked or clusterted bar chart. 

 

Desired Result:

 

2019 March

2020 March 

 

2019 April

2020 April 

 

2019 May

2020 May

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Use if statement to make conditional judgment and calculate data of each month.

    Measure =
IF (
    (
        YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () )
            || YEAR ( MAX ( 'Table'[Date] ) )
                = YEAR ( TODAY () ) - 1
    )
        && MONTH ( MAX ( 'Table'[Date] ) ) <= MONTH ( TODAY () )
        && MONTH ( MAX ( 'Table'[Date] ) ) >= 3,
    SUM ( 'Table'[Sales] )
)

    Sample .pbix

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , You use last year trailing measure or datesmtd 1 year behind. All that with a date table. Move your mon-year in that table

     

    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak thanks for the quick response!

       

      I used the both formulas you provided but I am only seeing 2020, not both 2019/2020 side by side. 

       

      last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
      last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

       

       

       

       

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Use if statement to make conditional judgment and calculate data of each month.

    Measure =
IF (
    (
        YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () )
            || YEAR ( MAX ( 'Table'[Date] ) )
                = YEAR ( TODAY () ) - 1
    )
        && MONTH ( MAX ( 'Table'[Date] ) ) <= MONTH ( TODAY () )
        && MONTH ( MAX ( 'Table'[Date] ) ) >= 3,
    SUM ( 'Table'[Sales] )
)

    Sample .pbix

     

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