Return previous months range when multiple months selected

[Anonymous]

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?

[Anonymous · Accepted solution]

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]

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!

[amitchandak]

@Anonymous , Try like

_Inc Count PM = CALCULATE('Incidents'[_Inc Count], dateadd('DATE Table'[Date],-1, month))

make sure DATE Table is marked as Date table in power bi

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4