cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VIZYUL
Frequent Visitor

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

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
)
4 REPLIES 4
wdx223_Daniel
Super User II
Super User II

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

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

amitchandak
Super User IV
Super User IV

@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"
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

VIZYUL
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors