Bug in Datediff()? How to calculate age of Invoice.

[croberts21]

I have Power BI Desktop vMarch 2023, for Windows. 

I do not have a calendar table. I have an invoice table with a Proforma Date. I'm calculating the date of the Proforma based on this: Proforma age = DATEDIFF(Invoice.proformadata, TODAY(), DAY) to get the number of days. 

My Proforma Date is a new colum which is: Proforma Date = Invoice.[invdate].date. This date is formatted as mm/dd/yyyy (short date) in the formatting bar. 

One Proforma Data is listed as 10/13/23, today is 10/24/23. The age PBI gives me is 286 days. The age should be 11. All other ages for all proformas are also too high. 

I have tried this formula in 2 different PBIX files and the result is both incorrect, and the same, the age in days is too high.

Am I doing this wrong? Should this be a measure instead of a column? 

When I try to make a measure for this I get this error: "A single for column 'Proforma Date' in table 'Invoice' cannot be determined."

Thank you.

[mlsx4]

Hi @croberts21 

Not sure if it is the problem, but I think you need to aggregate the value. Something like:

Proforma age = DATEDIFF(TODAY(),MAX('Table'[Invoice.proformadata]),DAY)

[croberts21 · Accepted solution]

Well darn. In the visual, in the Columns area, the field was set to Sum. I set it to "Don't summarize" and it works. 

[croberts21]

Hmm. I'll try it. When I created a new measure it must be recalculating the date for 1000s of invoices, which can take me 10 minutes or more. I'll have to wait to see if it works.