Forum Discussion
Responsive ResidentHelp 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
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/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __PreviousThere 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-TITHW/m-p/434008RichardJ , 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-64b0b4a4090eTo 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
3 Replies
- Greg_Deckler
Community Champion
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/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __PreviousThere 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-TITHW/m-p/434008
RichardJGreg_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
- amitchandak
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))))Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090eTo 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.