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
How to create index within a grouping in a matrix
Hi, I'm trying to create a measure that results in the Index column above. Assume that there is a matrix with a few layers in it, Region, SubRegion, Fiscal Year, and then Accounts. I would like the Index to start at the Fiscal Year Level. See above for reference. Repetition across the level below (Accounts) may or may not be needed. Thank you!
Index Column Groupby() and currentgroup()
Hi all, i have a table with the columns ID and Category. The goal is to create a new table that lists the IDs, and an index of the number of categorys an ID had in its lifetime. Example: Data Table ID / Category 00001 / SWF Info 00001 / Response FOMA 00001 / Creditreform 00001 / Legal IKU 00001 / Bankcrupcy Desiered Result should be: ID / Index 00001 / 5 I tried this
Create DAX index between 2 dates in a date table
Hi everyone, I need some help with the creation of an index between 2 dates. I've created an automatic date table using the Calendarauto() function and I'd like to create an index in a calculated column between 2 dates (starting at 0). I'm using the RANKX function as below to create an index starting on June 6, 2019 and ending on August 29, 2019 but the index is starting at 157 instead of 0. Index 1 = VAR StartDate = DATE(2019,06,06) VAR EndDate = DATE(2019,08,29) Return IF( 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate, RANKX('Date','Date'[Date],,ASC)) Does anyone have any idea how I can achieve this? Thank you for your help!
Get previous value from indexslicer dynamically with double filter context
I am creating a report for which I use and index slicer. I want to compare a value from the current selection with the value previous to the current selection. Below you'll find an example of my data. i want to create a measure that shows me the value for the "money" from the selected index value and the index value previous to the selected one. Also i want to have a measure for the text value from the selected index value. I tried the formula below, but it does not seem to work. I only get a value for selected index filter, but not for the previous one. Can somebody help me? Previous score = CALCULATE( SUM( 'subject_score'[Score]), Index[index] = MAX(Index[index) - 1), FILTER('subject_score', 'subject_score'[subject] = "Money")) Thanks!Calculating pondered quota achievement for N months.
Hey guys, I'm with a challenge here, and I'm not able to solve it with DAX so far. Thanks in advance for any help provided. I have 2 main fact tables: Sales (with won date[date of commercial commitment], payment date[the date my customer has paid me], and the name of the salesperson) and Monthly Quota table (each salesperson quota for each month, that considers won date), all they have a date column like MMM-YYYY where I can relate the sum of sales won in a month with that month's quota. I've as well two dim tables: Date and "Comission Rules". The comission rule is the same for all salesperson, is based on quota achievement Ex.: having $1.000.000 of sales and $1.100.000 of quota, it means I have 110% or 1.1 of achievement, for a given salesperson on a given month. (it's a function, a business rule that is not proportional, ex.: 30% of quota achievement = 0% comission, 80% of quota = 80% of comission, and 110% of quota = 130% of comission). I have a table for this but I could also usa a "big IF", it's not that complex. The output I need is the amount of comission that my backoffice need to pay for each salesperson that month. It's given by the sum of the comission from all sales that were paid in the earlier month for that salesperson. I don't care here about the won date, there are sales with a lot of different won dates being paid at the same month. But I need to know the quota achievement on each of that months. Example: - We are in October 2021 (M0), so I want to fetch all deals from Sales table that customers paid in Sep-2021 (M-1). I'll slice a date filter fot that. - Among the sales paid in Sep-2021, there are many different won dates (Ex.: Sep, Aug, Jul, and maybe more (M-1, M-2,... M-N)). - I need to assure for each of those months if the quota is achieved, and how much. This have to happen for "N" months, since I may have any number of months here. The quotas are different per month and different per salesperson, can't use averages here. - The output I need is something like having a date slicer selecting "won date" (format MMM-YYYY), and returning something like: = Divide((SUMX(sales), Filter (paid_date = SEP-2021)), (SUM(quota), FILTER (quota_date = SEP-2021))) + sum the same for one month earlier, and the month before, and so on until the end. From here I believe I can do the rest of the stuff, but I didn't manage to operate this so far. I'm not sure if it may be done with DAX, and I'm ok using several measures or creating new columns / tables if needed. Just let me know if I was no clear on something, and thanks everyone in advance. Ty, Daniel
