topic Re: Context issue when displaying prior year total that is based on date and category in DAX Commands and Tips

Context issue when displaying prior year total that is based on date and category

ToniT — Mon, 29 Jul 2024 19:50:18 GMT

My goal is to create a table that shows total applicants by action status for both selected and prior periods using the status that they had as of a selected date AND admission term. In other words, I want to count the students under the status for the row that is the the max effective dated row that is less than or equal to the selected date. I was able to do it for the selected values, but I'm having trouble showing the prior year in the same visual.

My data set is a list of student applications and their statuses (sample below). Each student has multiple rows of data that are effective dated but also have a sequence number in case 2 transactions happen on the same day.

What works: I have a measure that correctly calculates the number of applicants in each status as of the Admit Term and Date selected. I have also created a second measure based on this to calculate the prior year by finding that from the selected values (below). This measure appears to return the correct value when I EVALUATE it in DAX Query. If I return the variables as the output for the measure, they select the correct values. For example, if I select Fall 2024 and return _lastTerm, the measure returns Fall 2023.

Not Working: When I place both these measures in a visual together, the prior year shows up blank. I’m sure that this is a context issue, but can’t figure out how to fix this.

Any help will be appreciated.

Desired output is to show the corresponding counts for selected and prior Term/Date combination.

Measure (note: current year uses same measure but without the "last" variables)

Applicant Status Prior Year =

var _selectedDate = DATEVALUE(MAX('DimDate'[CalendarDate])) //get slicer value for effective date

var _selectedTerm = SELECTEDVALUE('Application data'[AdmitTermCode]) //get slicer value for Admit Term

var _selectedTermSeason = SELECTEDVALUE('Application data'[TermSeason]) //get associated Term Season

var _lastyear = //calculate same day last year

CALCULATE(

MAX('DimDate'[CalendarDate]),

SAMEPERIODLASTYEAR('DimDate'[CalendarDate]))

var _lastTerm = CALCULATE(MAX('DimTerm'[TermCode]) //get term code from last year

, ALL('DimTerm')

, 'DimTerm'[TermCode] < _selectedTerm

, 'DimTerm'[TermSeason] = _selectedTermSeason)

--filter app data to only row for selected term and before selected date

var _onlyBeforeDate =

SUMMARIZE(

FILTER(ALL('Application data')

, 'Application data'[AdmitTermCode] = _lastTerm

&& 'Application data'[EffectiveDate] <= _lastyear)

, 'Application data'[StudentNumber]

, 'Application data'[z_PartitionKey]

, 'Application data'[AppSequence])

-- Determine the most recent row before the selected date for each application

var _maxEffectiveDate =

INDEX(1

, _onlyBeforeDate

, ORDERBY('Application data'[AppSequence], DESC)

, PARTITIONBY('Application data'[z_PartitionKey]))

return CALCULATE(DISTINCTCOUNT('Application data'[StudentNumber]), _maxEffectiveDate)

Model:

Example of Data:

Note that multiple admit terms are accepting applications on any given calendar date, so date can't be the only value used to define the current/prior periods.

StudentNumber	ApplicationNumber	AdmitTermCode	TermSeason	AppSequence	z_PartitionKey	EffectiveDate	ProgramActionCode
123456	560495	1241	Fall	1	123456_560495	2024-05-27 0:00	APPL
123456	560495	1241	Fall	2	123456_560495	2024-05-28 0:00	ADMT
123456	560495	1241	Fall	3	123456_560495	2024-06-03 0:00	MATR
123457	558997	1241	Fall	1	123457_558997	2024-05-04 0:00	APPL
123457	558997	1241	Fall	2	123457_558997	2024-05-05 0:00	ADMT
123457	558997	1241	Fall	3	123457_558997	2024-05-17 0:00	MATR
123456	504500	1231	Fall	1	123463_504500	2022-10-31 0:00	APPL
123456	504500	1231	Fall	2	123463_504500	2023-02-01 0:00	APPL
123456	504500	1231	Fall	3	123463_504500	2023-02-01 0:00	ADMT

Re: Context issue when displaying prior year total that is based on date and category

some_bih — Tue, 30 Jul 2024 06:56:05 GMT

Hi ToniT I tried to use your sample of data and reuse measure, but without success as in measure there is reference to table/s DimTerm etc.

For sure, model seems ok (star schema) which is good start for measures.

Nevertheless, I tried to figure out what is your request, no matter what is data.

From your sample data, only (not other table/s) for StudentNumber = 123456 is it possible to get answer for your request? If yes please let me know what is expected output for your scenario.

Re: Context issue when displaying prior year total that is based on date and category

ToniT — Tue, 30 Jul 2024 14:42:51 GMT

Hi, thanks for looking at this!

For ID 123456, if I select a term of 1241 and a date of 2024-06-01 this student is counted in the ‘current’ measure under the Action code of ADMT. Based on the selection, the prior period would translate to term 1231 and a date of 2023-06-01, so I would expect to this application counted in the prior term also with a status of ADMT because that is the max row in the partition with the highest sequence number. My measures don’t use effective date because it is possible to have 2 transactions on the same day, so I ORDER the partition by the AppSequence number instead.

I don't have the ability to upload a file, but I do have a working .pbix example. I will try to get a link to it using Dropbox.

In the meantime, the DimTerm dimension looks like this:

DimTerm[TermCode]	DimTerm[AcademicYear]	DimTerm[TermSeason]
1233	2023	Spring
1231	2023	Fall
1230	2023	Summer
1232	2023	Winter
1240	2024	Summer
1242	2024	Winter
1241	2024	Fall
1243	2024	Spring

Re: Context issue when displaying prior year total that is based on date and category

ToniT — Tue, 30 Jul 2024 19:24:46 GMT

Here is a link to an example .pbix file. dropbox for example

For StudentNumbers can have multiple applications in multiple terms, but the z_PartitionKey and AppSequence fields will return distinct groupings and orders.

thanks!

Re: Context issue when displaying prior year total that is based on date and category

some_bih — Wed, 31 Jul 2024 13:24:26 GMT

Hi ToniT I will take a look and let you know 🙂

Re: Context issue when displaying prior year total that is based on date and category

some_bih — Thu, 01 Aug 2024 06:32:21 GMT

Hi ToniT I am currently checking your file.

Did you notice that your slicer for CaledarDate is style "Before", so this mean it showing data before 2.5.2024 not exact date, so this could affect your logic and calculation.

In addition, do you really want result per date, like for same date previous year? If so, at least for example file, for AdminTermCode=1241 there are dates: min=28.12.2023 and max=17.07.2024, so if you filter any dates in year 2024 there is no same date for previous date (2023). This could mean that your measure works fines, eventually, but as there is not data for previous year same date, the blank is ok?

AdminTermCode=1241

Re: Context issue when displaying prior year total that is based on date and category

ToniT — Thu, 01 Aug 2024 15:43:48 GMT

Thanks for looking at this.

I used Before in the slicer because when I choose a specific date I only select transactions that happened on that date, and not all the transactions that happened earlier in the admission cycle. In my data set, I want to count all applications that have any row before the selected date, but only count them once per application, and count them in the category (Action) associated with the row that is closest to the selected date.

The measure called Applicant Status by Date works 100% correctly with this data set and interacts with the slicers properly. I just can’t also display Applicant Status Prior Year in the same visual. I don’t want to count every row for each application under every status, which looks like what CountDistStN does in your example.

In the screenshot below, I’m able to achieve what I’m trying to do by including 2 different sets slicers and tables. I was hoping to display both in the same table so that users don’t have to select multiple times, but having done this, there may be value in allowing them to compare any two dates like this and not just prior year.

Re: Context issue when displaying prior year total that is based on date and category

some_bih — Thu, 01 Aug 2024 16:14:57 GMT

Hi ToniT ok for Before, I know more now.

So measure Applicant Status Prior Year, should show

Same as measure Applicant Status by Date but up to 1 year earlier?

Re: Context issue when displaying prior year total that is based on date and category

ToniT — Thu, 01 Aug 2024 16:22:48 GMT

Yes, that's it exactly!

Re: Context issue when displaying prior year total that is based on date and category

some_bih — Thu, 01 Aug 2024 19:42:07 GMT

Hi ToniT ok.

I am tomorrrow one day out of office, but I will check it and let you know, start of next week the latest