HI - I am trying to build a new measure which returns the calculated value for the previous month.
This is the initial measure I am working with which returns the correct data:
MeetingsPerDay = DIVIDE(COUNTROWS(MeetingsDashboard),DISTINCTCOUNT(MeetingsDashboard[localStartDate].[Day]))
I then tried to create a new measure to calculate the answer for the previous month as follows:
MeetingsPerDayLastMonth = CALCULATE(DIVIDE(COUNTROWS(MeetingsDashboard),DISTINCTCOUNT(MeetingsDashboard[localStartDate].[Day])),PREVIOUSMONTH(MeetingsDashboard[localStartDate]))
but it returns a DAX error:
Calculation error in measure 'MeetingsDashboard'[MeetingsPerDayLastMonth]: A date column containing duplicate dates was specified in the call to function 'PREVIOUSMONTH'. This is not supported.
any suggestions please!
I need this so I can build up a KPI visual comparing this month to last month etc.
Go to Solution.
Please create a Calendar table covering your period ( for example 2 years ..) and link this table to your fact table by the date.
Then you can write your measure with the previousmonth function and calendar date.
Hope it helps otherwise give sample data and will help.
What do you mean by link the table?
It's the new date table. We usually create an independent date table in your scenario. Please give it a try.
Calendar = calendar(date(2017,1,1),date(2018,12,31))
Calendar = calendarauto()
Could you please mark the proper answers as solutions?
Power BI Super User, Greg Deckler, explains
Register by September 5 to save $200
Engage and empower students with Power BI!
Continue your learning in our online communities.