Forum Discussion
subtract current month data from previous month using the same field
hi guys,
so i have a table that looks at progress of projects
the data source is coming from a folder so i have a column called report date
currently there are 2 different excels july 2020 and august 2020.
how do i subtract the august data from the july data using the progress field.
how can i do this
| Report Date | Progress | Change |
| July 2020 | 50 | 0 |
| August 2020 | 75 | 25 |
This is a sample of what i have
the output should be, it is 0 for july because there is not june data.
i need a column that shows current month progress - last month progress
How can i do this
2 Replies
paulfink , if you can mark that date column or create a column like this and change data type to date
Date = "01 " & [Report Date]
Now you can use time intelligence with date table
Example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))diff = [MTD Sales]-[last MTD Sales]
other method is using month/date table and month Rank
Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))Refer
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
Appreciate your Kudos.paulfink 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 __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
