Forum Discussion
Previous Month Target
Hi everyone,
I've had a look around but I can't find the answer on the forums.
I am trying to duplicate the following measure, but change it so that it gives me the previous month to use as a target value in the KPI Visual:
Active Persons:
VAR mindate =
MIN ( 'Calendar'[Date] )
VAR maxdate =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
COUNTROWS ( DM_PERIODS_OF_CARE ),
ALL ( 'Calendar' ),
OR ( DM_PERIODS_OF_CARE[END_DATE] >= mindate, ISBLANK ( DM_PERIODS_OF_CARE[END_DATE] ) ),
DM_PERIODS_OF_CARE[START_DATE] <= maxdate
)
I have tried to wrap it in 'PREVIOUSMONTH' but can't seem to get it to work. I want to have the KPI show this month's number as the indicator, and have the target be last month's number.
Does anyone know how I can fix this?
Thanks,
Dan.
Hi Anonymous ,
Please create a Date table and create the below measures, then put the new measures and Date fields of Date table on visual.
Active persons this month = CALCULATE ( DISTINCTCOUNT ( 'Table'[PERSON_ID] ), FILTER ( 'Table', ISBLANK ( 'Table'[END_DATE] ) || 'Table'[END_DATE] > MAX ( 'Date'[Date] ) ) )Active persons last month = CALCULATE([Active persons this month],PREVIOUSMONTH('Date'[Date]))Active persons same month last year = CALCULATE([Active persons this month],SAMEPERIODLASTYEAR('Date'[Date]))Best Regards
Rena
4 Replies
- amitchandak
Super User
Anonymous , Is the calendar is marked at date table? Option right click of a table .
after that these should work
Last Day = CALCULATE(COUNTROWS ( DM_PERIODS_OF_CARE ), previousmonth('Calendar'[Date]))
last MTD Sales = CALCULATE(COUNTROWS ( DM_PERIODS_OF_CARE ),DATESMTD(dateadd('Calendar'[Date],-1,MONTH)))also check fo these resons
- You do not have all dates in calendar table.
- You may have a timestamp in your date and it is not visible because of the datatype date or the format that does not include time. Change the datatype to datetime and have a format that shows time to check.
- In above case, if you have date with timestamp. Create a new date column and join it with date calendar.
Sales Date 1= [Sales Date].Date
Sales Date 2= Date(Year([Sales Date]), Month([Sales Date]), Day([Sales Date])) Hi Anonymous ,
Could you please provide some sample data(exclude sensitive data) and your desired result in order to provide you suitable formulas for your scenario? In addition, please check if the solution in following threads can help you.
KPI use previous month as target goals
DAX Previous Month to date total is giving entire previous month's entire total
Best Regards
Rena
Hi Anonymous please see above the desired result. I want the KPI to show this month's active persons and have it comparing to last month's active persons. The same logic for this month vs same month last year.
The DAX code that is currently being used incorporates that the total active persons must show the persons as active in every month, not just one. Active is defined as having a START_DATE and no END_DATE, and they must be counted in each month where they have no END_DATE (for example, PERSON_ID 10 would be counted as active in Jan 09 and Feb 09)
Example data:
Thanks!
Hi Anonymous ,
Please create a Date table and create the below measures, then put the new measures and Date fields of Date table on visual.
Active persons this month = CALCULATE ( DISTINCTCOUNT ( 'Table'[PERSON_ID] ), FILTER ( 'Table', ISBLANK ( 'Table'[END_DATE] ) || 'Table'[END_DATE] > MAX ( 'Date'[Date] ) ) )Active persons last month = CALCULATE([Active persons this month],PREVIOUSMONTH('Date'[Date]))Active persons same month last year = CALCULATE([Active persons this month],SAMEPERIODLASTYEAR('Date'[Date]))Best Regards
Rena
