Measure giving wrong answer

Try this:

TotalUsers_6M = 
VAR _LATEST_MONTH = MAX('Date'[Fiscal Date]) 
Var _END_DATE = EOMONTH(_LATEST_MONTH,0)
VAR _START_DATE = EOMONTH(_END_DATE,-6)+1
VAR _MAXDATE = MAX(Sheet1[order_date])
VAR _VALUE = 
CALCULATE(
    SUM(Sheet1[Net Revenue GBP]),DATESBETWEEN('Date'[Fiscal Date],_START_DATE,_END_DATE))
VAR _RESULT = 
IF( EOMONTH(_MAXDATE,0) >= _LATEST_MONTH, _VALUE)

RETURN _RESULT

It can work with DATESINPERIOD if you protect your date by wrapping it around EOMONTH.

Basically you need to make it clear to the code that needs to take the full period of the month.

I re wrote your logic below.

Debug = 

VAR LATEST_MONTH = MAX(Sheet1[order_date])
Var _END_DATE = EOMONTH(LATEST_MONTH,0)

var final_date =
    if(
        ISBLANK(
            year(_END_DATE)
            )
    ,BLANK(), _END_DATE
    )
VAR DATES_IN_PERIOD_TABLE_PY = DATESINPERIOD ('Date'[Fiscal Date], final_date, -6, MONTH)

VAR _Result = 
CALCULATE(
    SUM(Sheet1[Net Revenue GBP]),DATES_IN_PERIOD_TABLE_PY)

RETURN
_Result