Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 ...
Anonymous's avatar
Anonymous
Not applicable
7 years ago
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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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?

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago
        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