Comparing performance (only completed days)

[bvanderwatt]

Good Day 

I am trying to compare this year's performance to last years performance. 

My data is live and therefore it's skewing my results as it's compare a full day last year to a non-completed day this year (Depending on the time of the day I update my data.) 

How can I amend the below formulas to only include only up until yesterday for both measures? Ideally I want the measure to only take into account "completed" days. 

THIS YEAR'S FORMULA:

MTD NSls TY (Incl today) =

CALCULATE(

'Combined Sales'[Sum of Inv+Order],

DATESMTD('Calendar'[Date]

)

)

LAST YEAR'S FORMULA:

MTD NSls LY (Incl today) =

VAR _TODAY = TODAY()

VAR _LY = EDATE(_TODAY,-12)

RETURN

CALCULATE(

'Combined Sales'[Sum of Inv+Order],

SAMEPERIODLASTYEAR('Calendar'[Date]),

'Calendar'[Date] <= _LY

)

[johnt75 · Accepted solution]

You could try

MTD NSls TY (Incl today) =
CALCULATE(
'Combined Sales'[Sum of Inv+Order],
DATESMTD('Calendar'[Date],
'Calendar'[Date] < TODAY()
)
)
 
MTD NSls LY (Incl today) =
VAR _TODAY = TODAY()
var endLastYear = EDATE(_TODAY,-12) -1
var startLastYear = EOMONTH( _TODAY, -13) + 1
RETURN
CALCULATE(
'Combined Sales'[Sum of Inv+Order],
DATESBETWEEN( 'Calendar'[Date], startLastYear, endLastYear)
)

[bvanderwatt]

Hi John 

How could I amend the formula for this year to not be dependent on my date slicer? 

The formula only works if I add the date slicer. 

However, the formula for the previous year is not dependent on my date slicer. 

[johnt75]

You could try

MTD NSls TY (Incl today) =
CALCULATE(
'Combined Sales'[Sum of Inv+Order],
DATESBETWEEN('Calendar'[Date], DATE( YEAR(TODAY()), MONTH(TODAY()), 1), TODAY())
)

[bvanderwatt]

Thank you very much.