cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
spartachek Frequent Visitor
Frequent Visitor

Last Year (LY) Measure Limited by Current Date

Capture.PNGHello All:

 

Would someone help me to build a measure [Sales 2018] that shows last year amount for a selected period?

 

In the example attached to this post, [Sales 2018] for June should be $34.7 M (June 1 - 25), not $42.6 M (sum through June 30, 2018).

 

If I select, for example, May and June [Sales 2018] should be the amount from May 1 through June 25, 2018. And so on.

 

The solutions I've seen so far focus on YTD calculations and are quite confusing. I am trying to build a measure with dynamically changing date rage based on the selection.

 

Thank you all in advance.

 

Alex

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Last Year (LY) Measure Limited by Current Date

Hi there.

 

Would you please show me the code (please format it first at www.daxformatter.com) for your [Sales 2018] measure? I understand June 25 is the current day. My suggestion would be this. Say you already have a simple base measure defined called [Sales 2018] that just returns the sum of sales from the fact table for any selection of attributes and only for the year 2018.

 

You can then build your measure like this:

 

var __currentDay = TODAY()
var __currentDayOneYearBack = EDATE( __currentDay, -12 )
var __sales =
	CALCULATE(
		[Sales 2018],
		KEEPFILTERS( 'Date'[Date] <= __currentDayOneYearBack )
	)
return
	__sales

This will or will not work OK depending on how [Sales 2018] is defined.

 

Best

Darek

 

 

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Last Year (LY) Measure Limited by Current Date

Hi there.

 

Would you please show me the code (please format it first at www.daxformatter.com) for your [Sales 2018] measure? I understand June 25 is the current day. My suggestion would be this. Say you already have a simple base measure defined called [Sales 2018] that just returns the sum of sales from the fact table for any selection of attributes and only for the year 2018.

 

You can then build your measure like this:

 

var __currentDay = TODAY()
var __currentDayOneYearBack = EDATE( __currentDay, -12 )
var __sales =
	CALCULATE(
		[Sales 2018],
		KEEPFILTERS( 'Date'[Date] <= __currentDayOneYearBack )
	)
return
	__sales

This will or will not work OK depending on how [Sales 2018] is defined.

 

Best

Darek

 

 

View solution in original post

spartachek Frequent Visitor
Frequent Visitor

Re: Last Year (LY) Measure Limited by Current Date

Hi Darek,

 

Your formula works!

How would you learn all this?

Super User
Super User

Re: Last Year (LY) Measure Limited by Current Date

To be honest... I'm a faithful student of the two probably greatest DAX gurus in the world: Alberto Ferrari and Marco Russo. I've been through their courses and their books. I read one book several times until I understand everything in the tiniest details.

 

If you want, you can check this out: www.sqlbi.com (I'd recommend the course Mastering DAX, if you really want to know what's going on and how to address the most difficult DAX questions). And also go to Amazon for their ultimate book on DAX: Ultimate DAX Guide.

 

It's been some years now that I've been using DAX to create some pretty complex models. DAX takes time. Sadly 🙂

 

Best

Darek

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 327 members 3,386 guests
Please welcome our newest community members: