cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors