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 add a new column for the below table and the value for the column to be based on the below criteria.
For Code A1, A2,A3,...
If in Criteria column MDR Planned Finish date < 28-Oct AND it has a criteria of MDR Forecast date and Forecast date > 28-Oct, the the NEW column value to be Overdue,
If in Criteria column MDR Planned Finish date > 28-Oct AND it has a criteria of MDR Forecast date and Forecast date > 28-Oct, the the NEW column value to be Not Overdue,
If in Criteria column is MDR Issued Date, then the ew column to be "issued.
Code | MDR Description | Criteria | Date | Status | New Column |
A100 | A1 | MDR Planned Finish Date | 28-Oct-20 | Planned by cut off | |
A100 | A1 | MDR Forecast Date | 05-Nov-20 | Not Issued | Overdue |
A200 | B1 | MDR Planned Finish Date | 10-Nov-20 | Planned for Future | |
A200 | B1 | MDR Forecast Date | 15-Nov-20 | Not Issued | Not Overdue |
A300 | C1 | MDR Planned Finish Date | 10-Nov-20 | Planned for Future | |
A300 | C1 | MDR issued Date | 27-Oct-20 | Issued | Issued |
Solved! Go to Solution.
you can try this. The DAX is a little bit long. Hope the logic is correct for your real data.
Column = if(ISBLANK(maxx(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR issued Date"),'Table'[Criteria])),if(MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])<=DATE(2020,10,28)&&MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Forecast Date"),'Table'[Date])>date(2020,10,28),"Overdue",if(MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])>DATE(2020,10,28)&&MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Forecast Date"),'Table'[Date])>date(2020,10,28),"Not Overdue")),"issued")
Proud to be a Super User!
you can try this to create a column.
please note i slightly changed your creteria.
If in Criteria column MDR Planned Finish date < =28-Oct AND, otherwise A1 will be blank instead of "overdue".
Column = if('Table'[Criteria]="MDR Forecast Date"&& 'Table'[Date]>date(2020,10,28)&&maxx(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])<=date(2020,10,28),"Overdue",if('Table'[Criteria]="MDR Forecast Date"&& 'Table'[Date]>date(2020,10,28)&&maxx(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])>date(2020,10,28),"NotOverdue",if('Table'[Criteria]="MDR issued Date","Issued")))
Proud to be a Super User!
Hi Ryan,
Getting very close now. Thanks for the reply.
Now how can I get Overdue, not overdue, issued values repeated for each record of A1, B1 C,... and not having blank lines on the new column.
Thanks,
Amir
you can try this. The DAX is a little bit long. Hope the logic is correct for your real data.
Column = if(ISBLANK(maxx(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR issued Date"),'Table'[Criteria])),if(MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])<=DATE(2020,10,28)&&MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Forecast Date"),'Table'[Date])>date(2020,10,28),"Overdue",if(MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])>DATE(2020,10,28)&&MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Forecast Date"),'Table'[Date])>date(2020,10,28),"Not Overdue")),"issued")
Proud to be a Super User!
Thanks for your help, It worked as expected.
@amirghaderi , Trya new column like
New column =
var _Planned = maxx(filter(Table, [Code] =earlier([Code]) && [Criteria] = "MDR Planned Finish Date"),[Date])
var _Forecast = maxx(filter(Table, [Code] =earlier([Code]) && [Criteria] = "MDR Forecast Date"),[Date])
var _issued = maxx(filter(Table, [Code] =earlier([Code]) && [Criteria] = "MDR issued Date"),[Date])
return
if(Status in {" Not Issued","Issued" },
Switch( True(),
_Planned< today() && _Forecast>today() , "Overdue",
_Planned> today() && _Forecast>today() , "Not Overdue",
not(isblank(_issued )) , "Issued"
)
, blank())
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |