cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
josipinho
Helper II
Helper II

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, and I want to show revenue for previous year.

 

So I just need the revenue for a given year and for the previous year, and so far Ive only needed the info for one year, so I could use the filter where I jhad --> year(today())-1

 

Now unfortunately when I have more years, it shows the same information everywhere, and I cant figure out how to make it work for every year, not just current year and previous year... 

1 ACCEPTED SOLUTION
CMAC_Terry
Helper I
Helper I

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

 

 

View solution in original post

2 REPLIES 2
CMAC_Terry
Helper I
Helper I

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

 

 

View solution in original post

Thanks for the reply. IT isn't exaclly what I was looking for tho. 

 

In my matrix i have years as column header and I just wanted to have a measure for that particular year and the year before it, 

 

for example

 

 2015      2016           2017
 CY  PY   CY PY          CY  PY

  7     6    10  7           12  10

 

So I just need a measure that calculates for previous and current year. I got it working by removing the years as column header so now Im showing just one year but that isnt a great solution either.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.