Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return previous months range when multiple months selected

How do I return the [_Inc Count] for the equivalent number of previous months based on the months the user selects (whether that is 1 or more)?   Details: I have a previous month formula as follow...
Previous month
previous period
previousmonth
  • Anonymous's avatar
    Anonymous
    4 years ago

    This is the solution I ended up going with (measures)...

    To get the date range:

    _Inc Count PMs = 
    VAR start_of_period = FIRSTDATE('DATE Table'[Date])
    VAR end_of_period = LASTDATE('DATE Table'[Date])
    VAR months_in_period = COUNTROWS(VALUES('DATE Table'[Year Month Name]))
    VAR start_of_previous_period = FIRSTDATE(DATEADD('DATE Table'[Date],-1*months_in_period,MONTH))
    VAR end_of_previous_period = LASTDATE(DATEADD('DATE Table'[Date],-1*months_in_period,MONTH))

    RETURN
    FORMAT(start_of_previous_period,"mm/dd/yy") & " - " & FORMAT(end_of_previous_period,"mm/dd/yy")
     
    To get the target value, replaced the RETURN section with this:
    RETURN
        CALCULATE(
            [_Inc Count],
                DATESBETWEEN('DATE Table'[Date],start_of_previous_period,end_of_previous_period),ALL('DATE Table'))
     
    Note:  the source of one of the values used above is 
    'DATE Table'[Year Month Name] = FORMAT([Date],"MMM-YYYY")
Anonymous's avatar
Anonymous
Not applicable
4 years ago

This is the solution I ended up going with (measures)...

To get the date range:

_Inc Count PMs = 
VAR start_of_period = FIRSTDATE('DATE Table'[Date])
VAR end_of_period = LASTDATE('DATE Table'[Date])
VAR months_in_period = COUNTROWS(VALUES('DATE Table'[Year Month Name]))
VAR start_of_previous_period = FIRSTDATE(DATEADD('DATE Table'[Date],-1*months_in_period,MONTH))
VAR end_of_previous_period = LASTDATE(DATEADD('DATE Table'[Date],-1*months_in_period,MONTH))

RETURN
FORMAT(start_of_previous_period,"mm/dd/yy") & " - " & FORMAT(end_of_previous_period,"mm/dd/yy")
 
To get the target value, replaced the RETURN section with this:
RETURN
    CALCULATE(
        [_Inc Count],
            DATESBETWEEN('DATE Table'[Date],start_of_previous_period,end_of_previous_period),ALL('DATE Table'))
 
Note:  the source of one of the values used above is 
'DATE Table'[Year Month Name] = FORMAT([Date],"MMM-YYYY")