Forum Discussion
How to get previous month value
Hi, I am trying to get the previous month's value to then create the % difference per month. The measure I'm using is:
thiss article would help you understand the reason why you have the .date in the first place.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time
for your code, you just use : dateadd(calendar_use[date] , -1 , month ) . no need for the .date since anw it does not exists anymore.
5 Replies
Hi RichOB
Auto Date/Time might seem convenient, but it's not considered a best practice. It generates a hidden date table for every date column in the semantic model, which can unnecessarily increase the model size. Instead, using a dedicated date table—properly marked as a date table—makes time intelligence calculations more efficient and easier to manage.
Try either of these two measures:
Total Revenue Previous Month = CALCULATE ( [Total Revenue], PREVIOUSMONTH ( Dates[Date] ) ) Total Revenue Previous Month2 = CALCULATE ( [Total Revenue], DATEADD ( Dates[Date], -1, MONTH ) )Please see the attached sample pbix.
time intelligence functions are not best to work on the auto datetime hierarchy created by power bi ) . ]
what you should do ( if possible ) , disable the auto daate time intelligence from the settings of power bi .
if you cant :
try this :
Prev = CALCULATE(DISTINCTCOUNT(Table[ID]), DATEADD(Table[Date].[Date], -1, MONTH), all(date_table))and mark the date table as date table.
read more about it here :
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/
hope this helps.
Hi Daniel29195 thanks for your reply. I've disabled the auto time intelligence and made a calendar. But when I get to the DATEADD part in the measure, the .[date] doesn't automatically appear in the list of date options and it gives me the error below.
why would this happen please?
thiss article would help you understand the reason why you have the .date in the first place.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time
for your code, you just use : dateadd(calendar_use[date] , -1 , month ) . no need for the .date since anw it does not exists anymore.
Hi,
This should work
Prev = CALCULATE(DISTINCTCOUNT(Table[ID]),PREVIOUSMONTH(Calendar[date]))To your visual, ensure that you drag Year and Month name from the Calendar table.
Hope this helps.
