Replacing EARLIER functionality to work in a measure
Hello, There was already existing topics about this subject but so far had no success with them. I would like to rank teams with selected season and gamedate with the following way: VAR _HomeGameNumber = // Game number based on gamedate only with no season condition RANKX(FILTER(TeamSchedule_, TeamSchedule_[HomeTeam] = EARLIER(TeamSchedule_[HomeTeam])), TeamSchedule_[gameDate], , ASC, Dense) VAR _HomeGameNumberPerSeason = // Game number based on gamedate and season RANKX(FILTER(TeamSchedule_, TeamSchedule_[HomeTeam] = EARLIER(TeamSchedule_[HomeTeam]) && TeamSchedule_[season] = EARLIER(TeamSchedule_[season])), TeamSchedule_[gameDate], , ASC, Dense) RETURN //The Measure should be able to switch between the two whether season is filtered //or not which is not possible with calculated column like below: IF(ISFILTERED(AuxSeason[Season]), _HomeGameNumberPerSeason, _HomeGameNumber) The goal here would be to replace the EARLIER() function to use in a measure. Would this work somehow with MAX() in a measure?
Return the value for the next minimum date if the value field is empty, and group by ID.
Hi, In dax, I would like to return the value for the next minimum date if the value field is empty, which should be group by ID. Also, need to return the coresponding value in the associated category colum. I have only one table and have attempted to use several measures, the example measure below does not work well when there are null values. Please help! CALCULATE ( MIN ( Query1[value]), FIRSTDATE ( Query1[date]) )
DAX Calculated Column Sequence of Event Actions and Sessions
I have dataset with users actions logs. We have next data collection logic - every user have it own user_id and we record log of event actions during user session. user_id session_id dateTime event 1 aa 2023-01-01 13:12:11 login 1 aa 2023-01-01 14:12:10 buy 1 bb 2023-01-02 11:12:10 page 2 cc 2023-01-01 10:11:01 login 2 gg 2023-01-03 11:12:11 logout 2 gg 2023-01-03 13:11:03 click 2 gg 2023-01-03 14:10:07 logout The main goal is prepare output and add 2 calculated columns: 1. with events action sequence during every user session and 2. with session sequence during all user lifetime by SQL. Expected output: user_id session_id dateTime event event_seq session_seq 1 aa 2023-01-01 13:12:11 login 1 1 1 aa 2023-01-01 14:12:10 buy 2 1 1 bb 2023-01-02 11:12:10 page 1 2 2 cc 2023-01-01 10:11:01 login 1 1 2 gg 2023-01-03 11:12:11 logout 1 2 2 gg 2023-01-03 13:11:03 click 2 2 2 gg 2023-01-03 14:10:07 logout 3 2 What was done from my side: 1. Successfully create column with events action sequence during session Event Action Sequence = COUNTROWS ( FILTER ( CALCULATETABLE ( Sheet1, ALLEXCEPT (Sheet1, Sheet1[session_id]) ), Sheet1[Date] < EARLIER ( Sheet1[Date] ) || ( Sheet1[Date] = EARLIER ( Sheet1[Date] ) && Sheet1[Time Action] <= EARLIER ( Sheet1[Time Action] ) ) ) ) 2. Sessions Sequence Order By each user - calculated column not prepared - I stucked on it. Only prepared time for each session when it started (minimum time for each session) Min Session DateTime = CALCULATE( MIN(Sheet1[dateTime]), ALLEXCEPT(Sheet1,Sheet1[session_id]) ) So the main help request, how to solve this and create one more column with sessions sequene number orderd ascending by timestamp for each user. Sharing link to my pbix file https://drive.google.com/file/d/1807l9E07oNJv9l5rKWXKxDoc6rniyiYG/view?usp=sharing And sharing link to my sample: https://docs.google.com/spreadsheets/d/1bb7VId8lJ-NQkF43YS739kfc5Ur-1cGy/edit?usp=sharing&ouid=103990807236416334574&rtpof=true&sd=true
Solving calcuated Column problem using dax
I am facing an issue with calculated column. This is sample data and here I want to achieve if isStoreClosed is Closed Status then I want to divide the Daily_Split Value by 6 and add this average value to the next 6 Retail Dates Daily_Split Value. I have created the Daily_Split2 Column and written the folloing dax to achieve the result Daily_split 2 = VAR Closed = CALCULATE ( SUM ( 'Daily Table'[Daily_Split] ) / 6, FILTER ( 'Daily Table', 'Daily Table'[IsStoreClosed] = "Closed" && 'Daily Table'[Store_Code] = EARLIER ( 'Daily Table'[Store_Code] ) && 'Daily Table'[Retail Date] = EARLIER ( 'Daily Table'[Retail Date]) && 'Daily Table'[Iteration] = EARLIER('Daily Table'[Iteration]) && 'Daily Table'[Category]= EARLIER('Daily Table'[Category]))) return IF ( 'Daily Table'[IsStoreClosed] = "Open", CALCULATE ( SUM('Daily Table'[Daily_Split])+ Closed, FILTER ( 'Daily Table', 'Daily Table'[Store_Code] = EARLIER('Daily Table'[Store_Code]) && 'Daily Table'[Retail Date] >= EARLIER('Daily Table'[Retail Date]) && 'Daily Table'[Retail Date] <= EARLIER('Daily Table'[Retail Date]) + 6 && 'Daily Table'[Iteration] = EARLIER('Daily Table'[Iteration]) && 'Daily Table'[Category]=EARLIER('Daily Table'[Category]) ) )) But this Code not giving me the desired result. I am not able to add the average value to the next 6 days. How do I fix this any help please.Find duplicates between "date selected" and "date selected" - 30 days
Hi! 🙂 I have date filters (slicer) for year, month and day in a model that uses a table with [ID] and [Date] I want to show the duplicates between the "Date selected" and ("Date selected" -30 days) This is the table: If i select "December 1st, 2022" it should only show these: Thank you!
Calculated column with sum as function
I'm looking for a formula that uses sums as logic that we know in Excel. I want to add a calculated column (orange) in the table below. That sums values of column value if the column values of date and color are equal. I once came across a formula with the EARLIER function, but I don't know the exact formula to get it to work.Determine the First of Two Actions
Hello, I have a table (‘1egh TSQL’) with columns of specific actions and dates on which those actions occurred. I used the formula below to calculate a new column called Fiscal Year, based on the following criteria: Must be the MAX Sequence for the project Must be the FIRST of the *Action “Application Finally Rejected” or “Application Approved” (whichever one occurs first) If the first two criteria apply and the *Date is 10/01/2020 – 09/30/2021, then Fiscal Year = “FY21” If the first two criteria apply and the *Date is 10/01/2021 – 09/30/2022, then Fiscal Year = “FY22” If the first two criteria apply and the *Date is 10/01/2022 – 09/30/2023, then Fiscal Year = “FY23” If nothing applies, then BLANK How can I correct my formula to only select the first action that occurs? In this example, FY22 should be listed next to the *Action “Application Finally Rejected”. Fiscal Year = VAR _sequence = '1egh TSQL'[Sequence] VAR _pjtnumber = '1egh TSQL'[FHA Number] VAR _action = '1egh TSQL'[*Action] VAR _table = FILTER ( '1egh TSQL', '1egh TSQL'[FHA Number] = _pjtnumber ) VAR _maxseq = MAXX ( _table, '1egh TSQL'[Sequence] ) RETURN IF ( _sequence = _maxseq && ( _action = "Application Finally Rejected" || _action = "Application Approved" ), SWITCH ( TRUE (), '1egh TSQL'[*Date] >= DATE ( 2020, 10, 1 ) && '1egh TSQL'[*Date] <= DATE ( 2021, 9, 30 ), "FY21", '1egh TSQL'[*Date] >= DATE ( 2021, 10, 1 ) && '1egh TSQL'[*Date] <= DATE ( 2022, 9, 30 ), "FY22", '1egh TSQL'[*Date] >= DATE ( 2022, 10, 1 ) && '1egh TSQL'[*Date] <= DATE ( 2023, 9, 30 ), "FY23", BLANK () ), BLANK () )
