cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nweic
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
VijayP
Super User I
Super User I

@nweic 

 

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!


View solution in original post

Hi @nweic ,

 

Right click the table name and select create new measure.

V-lianl-msft_0-1602751991726.png

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

V-lianl-msft_1-1602752047877.png

 

 

Best Regards,
Liang
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

5 REPLIES 5
VijayP
Super User I
Super User I

@nweic  https://www.youtube.com/watch?v=dLON3mj0los

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!


amitchandak
Super User IV
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!

VijayP
Super User I
Super User I

@nweic 

 

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!


View solution in original post

nweic
Frequent Visitor

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

Hi @nweic ,

 

Right click the table name and select create new measure.

V-lianl-msft_0-1602751991726.png

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

V-lianl-msft_1-1602752047877.png

 

 

Best Regards,
Liang
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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors