cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MariemArfaoui
Helper II
Helper II

How to only display the value of the last available month

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

displayLastValueOnly.PNG

 

 

 

 

 

 

 

 

 

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

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @MariemArfaoui ,

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

 

growth.png

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.

 

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

Hi @MariemArfaoui ,

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

 

growth.png

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.

 

View solution in original post

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

 

amitchandak
Super User IV
Super User IV

@MariemArfaoui , do you have Date or month

Try to get max month like

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



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!

@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 

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.