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

## show value of second last month

Hi all,

i'm fairly new to PowerBi.

On our dashboard we'd like to always show the second last month and this month data on a card each.

I understand for "last month" i could use filters. But for second last i have to use formulas.

Could you please state the formula necessary and as well where i can put in the formula?

Much appreciated, KR,
Nick

2 ACCEPTED SOLUTIONS
Super User I

For 2nd Last month ( I mean Previous month of Previous Month if I am Clear)

CALCULATE(Sum(the value column),DATEADD(DATEDIM[DATE],-2,MONTH)

This can get you the resutl

Please share your Kudoes and if you find this as solution please mark this as solution.

Also you can watch Videos at www.youtube.com/perepavijay

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Community Support

Hi @nweic ,

Right click the table name and select create new measure.

Paste the formula here and modify the table or column name in the formula

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Super User I

Watch this Video to understand  Time Intelligence Completely

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Super User IV

@nweic , With date table and time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

last to last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

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!

Super User I

For 2nd Last month ( I mean Previous month of Previous Month if I am Clear)

CALCULATE(Sum(the value column),DATEADD(DATEDIM[DATE],-2,MONTH)

This can get you the resutl

Please share your Kudoes and if you find this as solution please mark this as solution.

Also you can watch Videos at www.youtube.com/perepavijay

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Frequent Visitor

Hi Vijay, and where do i paste the formula to?

Community Support

Hi @nweic ,

Right click the table name and select create new measure.

Paste the formula here and modify the table or column name in the formula

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

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

#### Microsoft Business Applications Summit sessions

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

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors