Forum Discussion

josipinho's avatar
josipinho
Helper II
8 years ago
Solved

Basic previous year measure

Hello folks, I have a question about something that i used year(toda()) up until now.   I need to show in a matrix data about revenue.   I want to have a matrix that shows 2017, 2018, 2019 etc, a...
  • CMAC_Terry's avatar
    8 years ago

    I've had similar problems this week, but I was defining Fiscal years and making a date input in a calculate formula relative to todays date. I came accross this blog that might help:

     

    https://www.fourmoo.com/2016/09/07/create-dynamic-periods-for-fiscal-or-calendar-dates-in-power-bi/

     

    However for me I wanted a simplier solution than discussed in the blog, this is how I have resloved the problem.

     

    Firstly you need a to create a power BI calender and relate it to the date column in your sales table; there are lots of blogs on that if you don't already have.

     

    Then I've created  (measures) for relative variable year start and end e.g.

     

    This year

    Var TY Start = Date(Year(Today()),01,31)

    Var TY End = Date(Year(Today()),12,31)

     

    Last Year

    Var LY Start = Date(Year(Today())-1,01,31)

    Var LY End = Date(Year(Today())-1,12,31)

     

    Then I used the variables in the Calculate Measure:

     

    LY Sales = Calculate(sum([sales]),

                                     Datesbetween(Dates[Calender],

                                     [Var LY Start], [Var LY End]

                                     ) 

    So to go 2 years back I created more variables for start and end and -2 of the year etc. This is working for me. I know there are probably alot cleaner and smarter ways of doing this, but this was a quick fix for me to create a variable relative to todays date.

    Terry