Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.