Context issue when displaying prior year total that is based on date and category
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
Problem with Running Totals for Prior Year
Hi, I'm struggling with the DAX to calculate the running total for the Prior Year. The Running total for the current Financial year works fine though. To get this working I have Two date tables as it always have to show the YTD data regardless of the month I select in the financial year. These are the results I'm getting. The Running total for the Prior Year is always blank and only shows up in previous Financial Year. Month Measure Measure Running Total Measure STLY STLY Running Total Jul 1403 1403 1248 Aug 1462 2865 604 Sep 951 3816 884 Oct 841 4657 1099 Nov 965 5622 1253 Dec 778 6400 1272 Jan 494 6894 839 Feb 632 7526 1276 Mar 1119 8645 1088 Apr 856 9501 1220 May 1253 10754 1339 Jun 1136 11890 1110 This is the Joins to the 2 Date Tables This is the DAX I'm using: //Current Year DAX [MeasureValue]= CALCULATE(SUM('MeasureData'[MeasureResult])) //Current Year Value [Measure] = VAR YearSelected = SELECTEDVALUE('DimDate'[Year]) RETURN CALCULATE([MeasureValue], FILTER(ALL(DimDate), DimDate[Year] = YearSelected)) //Current Year Running Total [Measure Running Total] = CALCULATE([Measure1], DATESYTD(DimDateYTD[YTDDateValue], "30/06")) // Prior Year DAX [Measure STLY] = VAR YearSelected = SELECTEDVALUE('DimDate'[Year]) - 1 RETURN CALCULATE([MeasureValue], FILTER(ALL(DimDate), DimDate[Year] = YearSelected)) // Prior Year Running Total [Measure STLY RunningTotal] = CALCULATE([Measure1 STLY], DATESYTD(DimDateYTD[YTDDateValue], "30/06")) Can anyone assist me with this please!!
