Calculating difference in a variable based on two different dates selected from a slicer

[sbeldona]

I have a data set that contains values of enrollment for 100s of courses by date as shown below.  And typically these dates go for about 40 days before class begin.

What I am trying to figure out is how can I show the delta (difference) between enrollment by course when a user selects a range of dates.  Any help would be greatly appreciated.  And I am very new to DAX.  So if there is anyone who can help me here it would be appreciated.

Course	Enrollment	Date
ABC	18	5/1/2019
XYZ	9	5/1/2019
ABC	19	5/2/2019
XYZ	9	5/2/2019
ABC	20	5/3/2019
XYZ	12	5/3/2019
ABC	24	5/4/2019
XYZ	18	5/4/2019
CDF	8	5/5/2019
ABC	25	5/5/2019

[v-xicai]

Hi @sbeldona ,

Try to create measure using DAX below. Or could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Differ = CALCULATE(MAX(Table1[Enrollment]),FILTER(Table1,Table1[Course]=MAX(Table1[Course])&&Table1[Date]=LASTDATE(Table1[Date])))-CALCULATE(MAX(Table1[Enrollment]),FILTER(Table1,Table1[Course]=MAX(Table1[Course])&&Table1[Date]=FIRSTDATE(Table1[Date])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sbeldona]

@v-xicai 

Hi Amy, thanks for your reply.  Here is the link to a masked sample data 

https://1drv.ms/x/s!AtSzBDgGn5HqfKpYnzs0uaOi5DQ

What I need is something like this in BI to display based on the Enroll Dates choosen by a user.  In this case Date1 and Date 2 are choosen by a user on a slicer that is made avialable.

Subject	Date1	Date 2	Differ
ACCT	30	35	5
BUAD	40	47	7
BANA	43	50	7
MANA	50	54	4
Total	163	186	23

Also, is it possible to create a line chart where on the X axis we have a Subject and on the Y axis are the DATE1 and DATE2 numbers.  Again, these DATE1 and DATE2 dates are choosen by a user using the slicer.

Your help would be so much appreciated.

[v-xicai · Accepted solution]

Hi @sbeldona ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards
Amy Cai

[v-xicai]

Hi @sbeldona ,

I am not sure about how to get the Date1 and Date2, is it referred to lowest and highest date of the range date slicer ? If yes, you can create measures [Date1 ] and [Date2] using DAX below.

Date1= CALCULATE(MAX(Table1[Enrollment]),FILTER(Table1,Table1[Course]=MAX(Table1[Course])&&Table1[Date]=FIRSTDATE(Table1[Date])))

Date2 = CALCULATE(MAX(Table1[Enrollment]),FILTER(Table1,Table1[Course]=MAX(Table1[Course])&&Table1[Date]=LASTDATE(Table1[Date])))

Differ = [Date2]-[Date1]

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sbeldona]

Thanks so much.