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 follows:

_Inc Count PM = CALCULATE('Incidents'[_Inc Count], PREVIOUSMONTH('DATE Table'[Date]))
which shows me the number of tickets from the month prior to whatever month they selected (from a filter showing a list of MMM-YYYY) if they select only 1 month.
 
But, if the user selects more than 1 month, how do I return the [_Inc Count] for the previous equivalent number of FULL months?  For example, if the user selects April-2022 and March-2022, I need to return the count for ALL of Jan and Feb (they selected 2 months so I need the two months before that range).  If they select Jan, Feb and March, I need it to return Oct, Nov and Dec.
 
I have a previous period formula that returns the equiv number of days but in this case, I need it to return full months even if one of the months selected is only thru the 4th or whatever.
 
Please help?
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")

3 Replies

  • 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")
  • karnold's avatar
    karnold
    Resolver I
    4 years ago

    Based on the details I produced an example on DAX.DO. You can experiment with it at -> https://dax.do/NwmaUnx44zszNB/ .

    I assumed you might be displaying a single value. 

     

    MEASURE 'Sales'[PreviousPeriodSales] =
        VAR _MinDate =
            MIN ( 'Date'[Date] )
        VAR _MaxDate =
            MAX ( 'Date'[Date] )
        VAR _NumMonths =
            ( DATEDIFF ( _MinDate, _MaxDate, MONTH ) + 1 ) * -1
        VAR _PrevEndDate =
            DATEADD (
                CALCULATETABLE (
                    VALUES ( 'Date'[Date] ),
                    REMOVEFILTERS ( 'Date' ),
                    'Date'[Date] = _MinDate
                ),
                -1,
                DAY
            )
        VAR _PrevStartDate =
            STARTOFMONTH (
                DATEADD (
                    CALCULATETABLE (
                        VALUES ( 'Date'[Date] ),
                        REMOVEFILTERS ( 'Date' ),
                        'Date'[Date] = _MinDate
                    ),
                    _NumMonths,
                    MONTH
                )
            )
        VAR _PrevDateRange =
            DATESBETWEEN ( 'Date'[Date], _PrevStartDate, _PrevEndDate )
        RETURN
            CALCULATE ( [Sales Amount], _PrevDateRange )

     If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!