cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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

2 REPLIES 2
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

Helper II

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors