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.
Hi,
I want to fill in the missing [Assigned Date].
How to create the [Assigned Date] with the below criteria:
If there is no [Assigned Date], then use [Created Date],
but if the [Created Date] is after [Onsite Date],
then use [Onsite Date] as [Assigned Date].
Assigned Date | Created Date | Onsite Date |
2/27/2020 | 2/26/2020 | 10/13/2020 |
- | 9/2/2020 | 9/8/2020 |
- | 9/12/2020 | 9/5/2020 |
Solved! Go to Solution.
Hi @PBI_newuser ,
May be modify the condition as follows and the try:
NewCol = IF([Assigned Date] = BLANK() && [Created Date] < [Onsite Date], [Created Date],
IF([Assigned Date] = BLANK() && [Created Date] > [Onsite Date], [Onsite Date], [Assigned Date]))
Thanks,
Pragati
You can do this in DAX by creating a new table with all of the dates in your range as follows: FullTable = ADDCOLUMNS( CALENDAR(MIN(Table1[Date]), MAX(Table1[Date])), "Quantity", LOOKUPVALUE( Table1[Quantity], Table1[Date], MAXX( FILTER(Table1, Table1[Date] <= EARLIER([Date])), [Date] ) ) )
The CALENDAR function gives you a range of dates from you minimum to maximum dates in your original table. From there, we add a new column, Quantity, and define it as the value we get when looking up the Quantity in the original table for the date that was the maximum date occurring on or before the date in the current row.
Hi @PBI_newuser ,
You can create a calculated column using the DAX below:
NewCol = IF([Assigned Date] = BLANK(), [Created Date], IF([Created Date] > [Onsite Date], [Onsite Date], [Assigned Date]))
Just make sure all the columns are in DATE format.
Thanks,
Pragati
Hi @PBI_newuser ,
May be modify the condition as follows and the try:
NewCol = IF([Assigned Date] = BLANK() && [Created Date] < [Onsite Date], [Created Date],
IF([Assigned Date] = BLANK() && [Created Date] > [Onsite Date], [Onsite Date], [Assigned Date]))
Thanks,
Pragati
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |