Help with Previous Month Price formula syntax

[RichardJ]

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

[Greg_Deckler · Accepted solution]

@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...

[amitchandak · Accepted solution]

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

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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...


Appreciate your Kudos.

[Greg_Deckler · Accepted solution]

@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...

[RichardJ]

@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