Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a data table similar to this (the first three columns). I would like to create a column that pulls in the previous week's date. I think from my research I should use the EARLIER function, but I'm not sure how to structure the formula. So, for each ID and week, I need to get the date for the previous week for the same ID.
Any thoughts?
Solved! Go to Solution.
Hi @heidibb,
If I understand you correctly, you should be able to use the formula below to create a column that pulls in the previous week's date in your scenario.
Previous Week Date = CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALL ( Table1 ), Table1[ID] = EARLIER ( Table1[ID] ) && Table1[Week] = EARLIER ( Table1[Week] ) - 1 ) )
Regards
Hi @heidibb,
If I understand you correctly, you should be able to use the formula below to create a column that pulls in the previous week's date in your scenario.
Previous Week Date = CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALL ( Table1 ), Table1[ID] = EARLIER ( Table1[ID] ) && Table1[Week] = EARLIER ( Table1[Week] ) - 1 ) )
Regards
Hey,
here is a part of my calendar already with the column you want to create "prevDate"
The column "Month Year" corresponds to your column "ID" and the column "Day of Month" to your column "Week" and here is the DAX statement
prevDate = var currentGroup = calculate(max('Calendar'[Month Year])) var currentDayIndex = calculate(max('Calendar'[Day Of Month])) return LOOKUPVALUE('Calendar'[Date], 'Calendar'[Month Year], currentGroup, 'Calendar'[Day Of Month], currentDayIndex -1)
I'm storing the column values of the current row into variables and then use these variables inside the LOOKUPVALUE function to retrieve the Date column by adjusting the currentDayIndex value with -1.
Hope this helps
Regards
As I continue to research, I wonder if LookupValue is a better function. Still unsure how to structure the formula since i need the previous week's date at the ID level.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |