Percent of difference with dynamic dates and drillthrough

[ellevy12]

Hi All

Help is needed. 

(for some reason all tables i post from excel are shown here from right to left , while they shoud be left to right - probably due to hebrew -english copy paste problems ... please note)

I want to show on a visual matrix the difference between number of registrants between all the dates the user selected in the slicer (meaning dynamic date filter) - if I have 3 dates: 1.1.2018 ; 1.5.2018 and 1.10.2018 I want to show the difference between 1: 1.5.2018 to 1.1.2018 and between 1.10.2018 to 1.5.2018.

I successfully managed to do this using the following DAX code: (when MyTable is the data table , Reg includes the number of registrants  and date hold the date. please note that dates can be any date and not a day before or month before etc. ...)

% registrants =

VAR previousDate =

    CALCULATE (

        MAX ( MyTable[Date] ),

        FILTER ( ALLSELECTED ( MyTable), MyTable[Reg] > 0 &&  MyTable[Date]< MAX ( MyTable[Date] ) )

    )

VAR previousREG =

    CALCULATE (

        SUM ( MyTable[Reg] ),

       FILTER ( ALLSELECTED( MyTable) ,  MyTable[Date]= previousDate )

    )

RETURN

           DIVIDE( SUM ( MyTable[Reg] ) -previousReg,previousReg,0)

 on the following dataset : 

Number of registrants	SCHOOL	FACULTY	SECTION	DATE
10	X	1	A	1.1.2018
20	Y	1	A	1.1.2018
40	Z	2	A	1.1.2018
50	T	3	B	1.1.2018
32	P	4	B	1.1.2018
40	U	5	B	1.1.2018
10	X	1	A	5.1.2018
20	Y	1	A	5.1.2018
10	Z	2	A	5.1.2018
10	T	3	B	5.1.2018
32	P	4	B	5.1.2018
40	U	5	B	5.1.2018

Up until this point all goes well - superb!  I get something like this (I put this table in excel to avoid starting to translate my entire dataset):

Number of registrants	date
0%	1.1.2018
-36.50%	5.1.2018

However , I want to add to my matrix the ability to drill through / expend to the levels of the following Hierarchy: 
this also work if I enable the user to filter the school , faculty , or section using a slicer

***PROBLEM starts here ***

1. Section 
2. Faculty 
3. School

it seems the drill through are not influence the ALLSELECTED dax statements. let say no filter were applied  I get the following visual:

result i get	% change expected	SECTION	date
0%	0%	A	1.1.2018
0%	0%	B
-79%	-42.90%	A	5.1.2018
-57%	-32.80%	B

calculation made by power bi is actually (40-192)/192 instead of (40-70)/70 , because the VAR previousREG for some reason is not filtered by the drill through/expend I made. 

so my question really is  -  can a drill through filter can applied and change this DAX code correctly ? I tried to change the code and remove the ALLSELECTED statement:

VAR previousREG = CALCULATE ( SUM ( MyTable[Reg] ),
 FILTER (( MyTable) , MyTable[Date]= previousDate ) 
)

However I received blanks instead of calculation results for some reason (why is that ?)

Thanks all for reviewing and trying to help

Elad

[v-lili6-msft · Accepted solution]

hi, @ellevy12

After my research, you could use ALLSELECTED  Function instead of ALLSELECTED Function in your formula as below:

% registrants = 

VAR previousDate =

    CALCULATE (

        MAX ( MyTable[Date] ),

        FILTER ( ALLSELECTED ( MyTable), MyTable[Number of registrants] > 0 &&  MyTable[Date]< MAX ( MyTable[Date] ) )

    )

VAR previousREG =

    CALCULATE (

        SUM ( MyTable[Number of registrants] ),

       FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) ,  MyTable[Date]= previousDate )

    )

RETURN

           DIVIDE( SUM ( MyTable[Number of registrants] ) -previousReg,previousReg,0)

Result:

CALCULATE (

SUM ( MyTable[Number of registrants] ),

FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) , MyTable[Date]= previousDate )

)

you can also add other conditions in it.

Best Regards,
Lin

[ellevy12]

Another question regarding to this data set -

how can i not display the min date ? since it's all 0 anyway due to not have diffrence of change ?

thanks 

[v-lili6-msft]

hi, @ellevy12

I have test a lot for it, I'm afraid it couldn't achieve for Matrix visual for now. The simplest method is filter it by slicer.

Sorry for the late reply for it.

Best Regards,

Lin

[ellevy12]

Amazing ! works ! i am struggling with this for a week (3 days of which i tried to understand how to login to the fourm using the new user system)