Check if the same date across different records is in the same month or not | Help and How-To

Jake Raz · 2026-04-02T14:51:40.199000Z

Hi! In our system we have Invoice data. There's an Invoice table with various details about the invoice, and each invoice also has multiple line items, tracked on a separate Line Item table. On the Line Item table is a field called "Service Date". I want to find Invoices that contain Line Item Service Dates that are in different months. This is against our billing guidelines, which say that invoices must only contain line items that are within the same calendar month as each other. I know how to do this in Excel using MAXIF and MINIF formulas to compare the earliest and latest Service Dates within a given Invoice. However, I can't figure out how to do this in Sisense. Please note: we're on an older Windows-based version of Sisense, v8.2.1.10110. I also do not have access to the ElastiCube configuration, so I cannot create new fields or modify the existing tables. Ideally I'd like to be able to do this within the existing UI in the Sisense front-end with formulas, if possible. I tried using this formula to render the difference in months as a number, with the idea that I could filter for anything greater than zero: MDIFF(MIN([Months in Invoice Line Item Service Date]),MAX([Months in Invoice Line Item Service Date])). However, the formula window in Sisense displayed an error and says "Error in function definition (MDiff): Expecting parameter of type 'Dimension Expression' but found 'Numeric Expression'." If I remove the MIN/MAX functions, so it's just MDIFF([Months in Invoice Line Item Service Date],[Months in Invoice Line Item Service Date]), the error message goes away, but then the formula doesn't work properly: it'll just display a "0" for all invoices, even ones that I know should show a 1 or more due to their service dates spanning across multiple months. (Presumably this is because, without the MIN & MAX, it doesn't know which particular Service Dates to compare.) If I try just subtracting the MIN from the MAX without using the MDIFF function, like MAX([Months in Invoice Line Item Service Date])-MIN([Months in Invoice Line Item Service Date1]), the formula window lets me submit the formula, but then the whole widget displays a yellow exclamation point error, and the error message says "Query number 0: types tinyint(8,0) and timestamp(7,0) are not equal". If I try just a MIN or MAX by itself, like MAX([Months in Invoice Line Item Service Date]), then I get a slightly different error: "Invalid cast from 'DateTime' to 'Double'. Couldn't store <date> in <bunch of numbers> Column. Expected type is Double." Is it truly not possible to calculate this? It seems like there should be at least some way to accomplish this, but I still can't figure it out. (I tried also asking various AI chatbots but none of them were helpful...)

MoreDiscussions

Check if the same date across different records is in the same month or not | Help and How-To | Sisense Community