Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

@Anonymous 

 

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 @Anonymous ,

 

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
Super User

@Anonymous  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
Super User

@Anonymous , 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)))

VijayP
Super User
Super User

@Anonymous 

 

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!


Anonymous
Not applicable

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

Hi @Anonymous ,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.