Power BI Webinar- How to take advantage of New Power Bi Functions Offset,Window,
Mark the Date. Power BI Webinar- How to take advantage of New Power Bi Functions Offset,Window, Index: https://youtube.com/live/exk8F5ka1Nw Saturday Feb 25, 2023 at 08:00 AM PST, 10:00 AM CST, 11:00 AM EST, 09:30 PM IST Till End
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!
