Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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

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

amitchandak
Super User
Super User

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors