cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Responsive Resident

## Help with Previous Month Price formula syntax

Hi,

Hopefully something simple but am struggling to work out how to find the previous months price - shown in yellow

Column to be populated

I've used the formula shown which isn't working but am unclear how the system would know to find the Material Number field to compare with the previous months price for that Material Number

currently incorrect formula

Once I have this value I'm confident I can work out the price difference/percentage difference/etc but am stuck until I can get his formula to work.

The pbix file can be found here : https://www.dropbox.com/s/olesbfpz85mcnpp/Previous_Month_Price.pbix?dl=0

Thanks for any assistance,

Richard

2 ACCEPTED SOLUTIONS
Super User

@RichardJ - So you can use the time "intelligence" function PREVIOUSMONTH like you are using but not sure about using that in a column forumula. Seem like you just need to use something like MTBF

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous

There are other ways. @ me if you need me to take a deeper dive.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

@RichardJ , previousmonth should be used with a date table and create a measure.  Column will not work like this

example

``````previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
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]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

``````

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

3 REPLIES 3
Super User

@RichardJ , previousmonth should be used with a date table and create a measure.  Column will not work like this

example

``````previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
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]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

``````

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Super User

@RichardJ - So you can use the time "intelligence" function PREVIOUSMONTH like you are using but not sure about using that in a column forumula. Seem like you just need to use something like MTBF

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous

There are other ways. @ me if you need me to take a deeper dive.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Responsive Resident

@Greg_Deckler  & @amitchandak  - Thanks for the comprehensive and quick responses.

Reading what you'd sent i'd gone wrong by not having a good Calendar table. I had tried to 'Mark as Date' the Stock Data table which didn't work due to containing duplicate date values.

Thanks to your help @Greg_Deckler I now have a column which works 'Previous Price Paid'

Thanks to your help @amitchandak I now have a measure which works ' Previous Month Cost'

New Column and Measure

The revised pbix file with the column and measure mentioned above can be found here:

https://www.dropbox.com/s/1h29r5hsg548ozg/Previous_Month_Price_v2.pbix?dl=0

in case it's of use to anyone else in future.

Cheers,

Richard

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.