Compare 2 dates, 1 from SP List created (upload date) and 1 from Excel file (Due Date)

[mdebekker]

I have a data source from a Sharepoint Library which contains the upload date from a file (created) which I want to compare with the Due date of uploading a file from an Excel table. I want to have a conditional formatting set when the date of upload is past the Due date. Searching here in the community didn't help. Hopefully with this new thread someone can help me.

  

[mdebekker]

It seems that the brackets were incorrect and now this part has been solved: 

On Time = If(max('Monthly Project Reports (Active)'[Created]) < MAX(DUE_DATES_BPO[Due date]),"1","2")

Still have some ongoing questions on how to set a number 3 when dates are the same. the other question is when using the numbers they are set as "text" format which I can't change as it's greyed out

[v-shex-msft]

HI @mdebekker,

You can try to remove the quotation that you set in your if statement results, then save changes and click on the measure field to reactivate it to confirm if these formatting fields activated.

Regards,

Xiaoxin Sheng

[amitchandak]

@mdebekker , You can create a color meausre like

if(max(table[upload date]) <max(Table[Due date]) , "green", "red") and use that is conditional formatting with field value option

[mdebekker]

Thanks for your reply, I entered the code: 

---

Status = IF(MAX('Monthly Project Reports (Active)'[Created] <MAX(DUE_DATES_BPO[Due date], "green", "red")))

---

I get the following error on that;

---

Too many arguments were passed to the MAX function. The maximum argument count for the function is 2.

---

[v-shex-msft]

HI @mdebekker,

How did your table record mappings? One : many? 
If the fact table keys have multiple corresponding date values stored in two tables, it is hard to mapping and compared, you need to do summaries with them first.

Regards,

Xiaoxin Sheng