How to calculate a measure for the most recent month of data WITHOUT using TODAY()

[VIZYUL]

I need help writing the correct DAX statement to SUM Sales for the last month of data in the Sales table when the latest month is NOT The same month as TODAY().  For example, the most recent month of data available is Feb of 2014.  Since it's currently November, I need help writing a dynamic DAX statement to calculate total sales for the lastest month available, no matter the Filter Context.

I'm using the AdventureWorksDW2019 data.  In that data set, there is data where DimProductCategory is either Accessories or Components, however there no Sales data for Bikes.  The calculations below work fine as long as there are no Slicers applied to DimDate.  The moment any year OTHER THAN 2014 is selected in a Slicer, the calculation below does not work.

Here are the calculations I'm using.  In my Date Table (DimDate) I'm using the following calculation to get the most recent month of data.  The calculations below work when there's NO FILTER CONTEXT.  Whenever I filter data using a year or month slicer, the calculations DO NOT WORK.

IsCurrentMonth =

VAR _lastsalemonth = MAX(FactInternetSales[DueDate])

VAR _monthstartDATE = DATE(YEAR(_lastsalemonth),MONTH(_lastsalemonth),1)

RETURN

DATE(YEAR(DimDate[FullDateAlternateKey]), MONTH(DimDate[FullDateAlternateKey]), 1) = _monthstartDATE

In order to calculate total Sales for the most recent month, this is the calculation I'm using.

CurrMo =

CALCULATE (

[Total Sales],

DimDate[IsCurrentMonth] = TRUE

)

[wdx223_Daniel]

@VIZYUL you can use this to get Max Date in Fact Table

=CALCULATE(MAX(FactTable[Date]),ALL(FactTable))

[amitchandak]

@VIZYUL , In your date table have column like this

Month Type =
Var _max = max(Table[Date]) //Assume the table that is source for max date
Switch( True(),
eomonth([Date],0)= eomonth(_max,0),"This Month" ,
Format([Date],"MMM-YYYY")
)

You can sort this a new column Format([Date],"YYYYMM")

or

Month Type =
Var _max = max(Table[Date]) //Assume the table that is source for max date
Switch( True(),
eomonth([Date],0)= eomonth(_max,0),"This Month" ,
"Other Months"
)

[kumar27]

Use ALL that will remove any filter context applied on the page .

[VIZYUL]

That's what I want to avoid kumar27.  I'm looking for a solution that maintains the current Filter Context