Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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