Skip to main content
cancel
Showing results for 
Search instead 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

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

 

 

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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