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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Pass two Date Ranges into DAX Formula

Hello!

 

My client wants to be able to input two dates to calculate sales for two periods, and then calculate the Delta %. The problem I'm having is how to incorporate two Date Parameters into the Delta formula (the Before & After periods).

 

To calculate the Delta formula, I want to take "Gross Sales P2" - "Gross Sales P1" / "Gross Sales P1". My thought was to somehow pass both date parameters into a single DAX formula to calculate this.

 

One idea I've had is to create two dynamic tables based on the two date ranges, and then reference those tables in the formula. But I don't know how to do that. We're connected to a SQL Server Analysis Services cube so I don't quite have the option of easily editing the data on the backend. We have a robust Calendar table to work with though.

 

 

My screenshot better explains what I'm looking at.

 

Capture.PNG

6 REPLIES 6
Anonymous
Not applicable

Growth % =
var __periodBefore =
	TREATAS(
		VALUES( BeforeSlicer[Date] ),
		'Dates'[Date]
	)
var __periodAfter =
	TREATAS(
		VALUES( AfterSlicer[Date] ),
		'Dates'[Date]
	)	
var __beforeGrossSales =
	CALCULATE(
		[Gross Sales],
		__periodBefore
	)
var __afterGrossSales =
	CALCULATE(
		[Gross Sales],
		__afterPeriod
	)
var __growth =
	DIVIDE(
		__afterGrossSales - __beforeGrossSales,
		__beforeGrossSales
	)
RETURN
	__growth

Best

Darek

This is helpful!  May I ask how you named the slicers as in the following statements?  Stated another way, not sure how to name my slicers as "BeforeSlicer" and "AfterSlicer".  Perhaps as a named variable?

Growth % =
var __periodBefore =
	TREATAS(
		VALUES( BeforeSlicer[Date] ),
		'Dates'[Date]
	)
var __periodAfter =
	TREATAS(
		VALUES( AfterSlicer[Date] ),
		'Dates'[Date]
Anonymous
Not applicable

EDIT: I'm using Analysis Services for my data source, so it appears these functions are not available to me.

 

Darak - I track with your code, but I'm not able to create variables or use the TREATAS function when creating a DAX formula in Power BI Desktop. Am I doing something wrong?

 

 

2.PNG

 

 

1.PNG

 

Anonymous
Not applicable

Well, whether or not you can use a function depends on the version of the SSAS. I hope you're using SSAS Tabular... not Multidimensional. If a function is not available, then you'll have to reformulate the code in such a way that it does the same thing but uses only the available constructs. TREATAS can be replaced by a construct with INTERSECT or, if the version does not support it, with CONTAINS. But pay attention to syntax.

 

Bestk

Dare

Anonymous
Not applicable

Would you have any suggestions on how to best utilize INTERSET in this scenario? I don't quite see how that function fits as a replacement for TREATAS. We're using a tabular model.

Anonymous
Not applicable

Growth % =
var __periodBefore =
	INTERSECT(
		VALUES( BeforeSlicer[Date] ),
-- You might need to replace ALL (...) with VALUES(...) depending on what your needs are.
-- If you do, then it means you'll respect any existing selections on the Dates dimension.
-- Using ALL ( ... ) only transfers the selection from BeforeSlicer and removes any filters
-- coming from Dates itself. You have to decide which behviour fits your req's. Same is
-- true for the AfterSlicer. ALL( 'Dates'[Date] ) ) var __periodAfter = INTERSECT( VALUES( AfterSlicer[Date] ), ALL ( 'Dates'[Date] ) ) var __beforeGrossSales = CALCULATE( [Gross Sales], __periodBefore ) var __afterGrossSales = CALCULATE( [Gross Sales], __afterPeriod ) var __growth = DIVIDE( __afterGrossSales - __beforeGrossSales, __beforeGrossSales ) RETURN __growth

Best

Darek

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors