How to only display the value of the last available month

[Anonymous]

Hello !

I would like to display the growth of the last monthly only without using a month filter or slicer

My data comes from an excel file that is updated monthly and I managed to create to following DAX forumla

Each time I would like to display in my table the value of the last month only  (without having the column of Month_ID). I created the formula :

var growthLastMonth = CALCULATE(growth3m, FILTER(DimTime, DimTime[MONTHDAY_DT] = lastMonth))

however it returns the wrong value, could you please offer me some advice on how the result I want ?

Thanks in advance,

Mariem

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If you only want to show last month value in the table visual without any other column fields, you can create measure like this based on your dataset:

Growth =
VAR lastmonth =
    MONTH ( TODAY () ) - 1
VAR lastmonthsales =
    CALCULATE (
        [Total],
        FILTER ( ALL ( 'Table' ), MONTH ( 'Table'[date] ) = lastmonth )
    )
VAR last3monthsales =
    CALCULATE (
        [Total],
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table'[date] ) <= lastmonth
                && MONTH ( 'Table'[date] ) >= lastmonth - 2
        )
    )
VAR growth3m = ( lastmonthsales - last3monthsales ) / last3monthsales
RETURN
    growth3m

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjl Thank you so much for your help, you input helped me to get the result I needed !

[amitchandak]

@Anonymous , do you have Date or month

Try to get max month like

var _1 = maxx(allselected(DimTime) ,DimTime[MONTHDAY_DT] )

[Anonymous]

@amitchandak , I have both in dimTime but i'm using the date type column to filter everything.

And i have tried your formula but it's not working , eventhough i get max value of the month, i won't be able to display that value without have the date as in attribute in my table