how to: Dynamic percentage change and # change using a formula using date filters as a reference

[Anonymous]

General report - will not calculate % change and # change if changed dates in filters due to hardcoded dates in formulasShowing connections to May filterShowing connections to June filter

This is the code for the % Change calc.

sum of A % difference from June 2017 = 
VAR __BASELINE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date])
	)
VAR __MEASURE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date])
	)
RETURN
	IF(
		NOT ISBLANK(__MEASURE_VALUE),
		DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
	)

This is the calc for the unit # difference: 

sum of A difference from June 2017 = 
VAR __BASELINE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date])
	)
VAR __MEASURE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date])
	)
RETURN
	IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE -  __BASELINE_VALUE)

 So I have two cards called A. They are the same summation measure. They show different numbers because they are each connected to a different date filter that you can see in those photos. Then I want to calculate a # change and percentage change from these two different values. I posted the formulas above. The issue is that the date is hard coded in both those formulas, so if I were to change my date slider to see Jan vs. Feb., for example, the # change and percentage change display as "(blank)". 

How can I accomplish a dynamic percentage change and # change using a formula that uses the date filters as a reference or some other solution? 

[v-sihou-msft]

@Anonymous

In this scenario, you have one slicer for Card A and another slicer for Card B. Now you want to show percentage change/difference based on the result in both Cards with selections from corresponding slicers. However, since you make different selection in slicers, you can pass two different filter context into same visual. For your requirement, you need to duplicate your date table and use USERELATIONSHIP() for two different calculations.

Please refer to article below:

Comparing Any Two Time Periods in DAX

Also see this similar thread.

Regards,

[Anonymous]

Thank you that was very helpful. I followed the guidance of this article (Comparing Any Two Time Periods in DAX). I ended up with this picture below, which is so close to what I was going for. 

sum of approval volume = sum(LNApps_Facts[Total_Approval_Volume]) 

Sum Total Approval Comparison Period = 

CALCULATE (
    SUM ( LNApps_Facts[Total_Approval_Volume]),
    ALL ( 'LNApps_AppDateDim' ),
    USERELATIONSHIP ( LNApps_Facts[AppDateDim_Key], 'Comparitive Date'[AppDateDim_Key] )
)

Measure = divide('My Calcs'[sum of approval volume]-'My Calcs'[Sum Total Approval Comparison Period] ,'My Calcs'[sum of approval volume])

These are the calculations behind all the measures you see displayed. 

Also the LNApps_Facts[AppDateDim_Key] is related to the LNApps_AppDateDim[AppDateDimKey] that is ACTIVE

The Comparitive Date [AppDateDimKey] is related to the LNApps_Facts[AppDateDim_Key] but is INACTIVE 

The Comparitive Date [AppDateDimKey] is related to the LNApps_AppDateDim[AppDateDimKey] but is INACTIVE also

These relationship connections were modeled after what was done in that article.

What I would like to see in that photo is not the overall % change of 91.46%, but the % change for the month selected in that timeline. Somehow I want to select not only January, but say what is the change from January to February which should show 9.41% as you can see in that table. How can I modify what I've done to reflect this?