cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lizsufrinko Member
Member

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

Screen Shot 2017-06-18 at 1.53.49 PM.pngGeneral report - will not calculate % change and # change if changed dates in filters due to hardcoded dates in formulasScreen Shot 2017-06-18 at 1.53.55 PM.pngShowing connections to May filterScreen Shot 2017-06-18 at 1.54.01 PM.pngShowing 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? 

2 REPLIES 2
Moderator v-sihou-msft
Moderator

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

@lizsufrinko

 

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,

lizsufrinko Member
Member

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

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. 1.PNG

 

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? 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,526)