Is there a better way to create Last Month aggregation measures?

DAX DAX Calculations
1

Hi Everyone,

I have actually created a solution but I am wondering whether there is a better, more efficient way of achieveing the outcome I am looking for.

What I am seeking to achieve is to create a series of summary cards on a visualisation that highlight key indicators for the lastest month, which is dynamically updated each month. I require a card that highlights the latest period (month and year) and cards for the the KPI’s. In the example attached ,I only created a card fror sales but there could be multiple cards.

The steps I took were.

To filter for the last month, I used the related function to copied the Month Number from the Dates table into the Sales table and then created the following measure

Last Month Sales = CALCULATE([Sales], FILTER(Sales, Sales[MonthNum] = MAX(Sales[MonthNum])))

The measure I used to highlight the last month was

Last Month = FORMAT(MAX(Sales[OrderDate]), “MMM YYYY”)

My question is that given the Dates table has colums for both the Month Number and MonthYear could these measure have been built directly addressing the Dates table rather than copying the Month Number colum into the Sales table.

PracticeModel.pbix (410.7 KB)

Thank you.

Michael Gordon

2

Hi @MIchaelGordon,

You are right, you don’t need to add that calculated column to your Sales table…
For example this will get you the same result.

Last Month v2 =
VAR LastSale = MAXX( VALUES( Sales[OrderDate] ), Sales[OrderDate] )
VAR LastMonth = CALCULATE( MAX( Dates[MonthnYear] ), FILTER( ALL( Dates ), Dates[Date] = LastSale ))
VAR SalesLM = CALCULATE( [Sales], FILTER( ALL( Dates ), Dates[MonthnYear] = LastMonth ))

RETURN
    SalesLM

I hope this is helpful

3

Hi @MIchaelGordon, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

Thank you Melissa